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! 😊
Hi @Fadem
What's your beast mode? Is it using the same column(s)? Are you getting any sort of error message?
@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`))
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`
@GrantSmith I was not getting an error message however, it worked after I expanded the formula editor! Thank you!
You're awesome! Thank you!
