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

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

amehdad

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

@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).

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

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

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. 😥

amehdad

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

@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).