How to use a calculated field in a SUM function? (Statistics)

ramoshe
ramoshe βšͺ️

Hello! Please someone help me with this error! πŸ˜₯

I am creating a table (card) with the mean, variance and standar error of a data column named "Score".

The mean is calculated like this and the value is 6.67:

If I calculate the variance using the value of the mean it works:

That's not the idea cause the mean can change, but if I try to use the mean calculated field I get this error:

I tried it on Excel and it does work. I tried creating a constant column with the mean drectly on the ETL but is not possible. I guess the error has something to do with the SUM function. Help!πŸ˜₯

Best Answers

  • amehdad
    amehdad 🟒
    Accepted Answer

    Hi @ramoshe, you'll need to use the 'Group By' tile to get your average (or any aggregation) field. Have a look at this article [https://domohelp.domo.com/hc/en-us/articles/360044876094-Magic-ETL-v2-Tiles-Aggregate-Beta-] to better understand how to configure 'Group By' before adding it in your ETL.

  • GrantSmith
    GrantSmith Indiana πŸ”΄
    Accepted Answer

    @amehdad21 beat me to it but he's correct, you'd use a Group By tile to aggregate your data grouping on whatever the partition you want to calculate the average across. If you want to do it across your entire dataset you'd need to add a constant value (Add Constant tile) in a new column to group by (Group By tile requires a column to group on and can't do the entire dataset).

Answers

  • MichelleH
    MichelleH 🟠

    Hi @ramoshe I'm curious why you say it wasn't possible to create the mean in ETL. What have you tried and what was the result?

    Beast mode doesn't always like nested aggregate functions, so I think your best bet will be to fine tune that mean calculation in ETL.

  • GrantSmith
    GrantSmith Indiana πŸ”΄

    Hi @ramoshe

    This is because you're attempting to aggregate (SUM) on an aggregate (AVG) which isn't possible within a beast mode. You'd need to calculate the average of your dataset in your ETL and then use the resulting column in your beast mode. The downside to this is that it won't be respective of any filters you apply to your card.

  • ramoshe
    ramoshe βšͺ️

    Hi @GrantSmith and @MichelleH thanks for answering. I don't know how to create the average column on the ETL. I tried using the calulator operator but i couldn't. There is not an average function or I don't know how else. πŸ˜₯