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

ramoshe
ramoshe Member
in Charting

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

image.png

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

image.png

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

image.png

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

image.png
Untitled Image

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

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    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).

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

Answers

  • MichelleH
    MichelleH Coach

    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 Coach

    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.

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

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

    image.png


  • GrantSmith
    GrantSmith Coach
    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).

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

Categories