Beast Mode vs Formula Tile on Magic ETL 2

Fadem
Fadem
edited February 2022 in Magic ETL

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! 😊

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Fadem

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Fadem
    Fadem
    Answer ✓

    @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
    GrantSmith Coach
    Answer ✓

    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`


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @Fadem

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Fadem
    Fadem

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

  • Fadem
    Fadem
    Answer ✓

    @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
    GrantSmith Coach
    Answer ✓

    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`


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Fadem
    Fadem

    You're awesome! Thank you!

Categories