Beast Mode vs Formula Tile on Magic ETL 2

Fadem

Happy Friday Dojo Community!

Does anyone know why a Formula would be validated by Beast Mode but show as an error for the Formula tile on Magic ETL 2?

Also, is there any online guide that could potentially be helpful?

Thank you! 😊

GrantSmith

Hi @Fadem

What's your beast mode? Is it using the same column(s)? Are you getting any sort of error message?

Fadem

@GrantSmith

I actually have another Beast Mode that is validated in the Cards but not the ETL:

My error message says function sum requires an aggregation context. Use group by action.

Beast Mode:

(Sum(`Amount` + `Employer_Taxes` + `Fringes`) - Sum(Case when Case when SUBSTRING(`Expense_Account`,4,2)>=75

then 'Indirect'

else 'Direct'

end = 'Direct' then 0 else `Amount` + `Employer_Taxes` + `Fringes`

end)) / (Sum(`Amount` + `Employer_Taxes` + `Fringes`))

GrantSmith

You can't use aggregates in the formula tile. It's processing data on a record by record basis. You'd need to break out your different component's you're aggregating into separate columns then feed that into a group by to do the aggregation (sum) and then do your final subtraction and divisions.

Total

`Amount` + `Employer_Taxes` + `Fringes`

Would be one column and your case statement would be a separate

You can also simplify your case statement:

Conditional Total

Case when SUBSTRING(`Expense_Account`,4,2)>=75

then `Amount` + `Employer_Taxes` + `Fringes`
else 0

end)

Group by whatever your key fields are and tell it to SUM your Total (new column: Grand Total) and Conditional Total (new column: Grand Conditional Total) fields

Then feed the group by into another formula tile and do:

(`Grand Total` - `Grand Conditional Total`) / `Grand Total`

Fadem

@GrantSmith I was not getting an error message however, it worked after I expanded the formula editor! Thank you!

Fadem

You're awesome! Thank you!