How to use a calculated field in a SUM function? (Statistics)
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
-
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.
9 -
@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!**2
Answers
-
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!**2 -
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. 😥
0 -
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.
9 -
@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!**2
Categories
- 10.5K All Categories
- 4 Connect
- 914 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 183 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 170 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive