How to calculate best?
Hi all,
I am pretty new to DOMO and would like to know if there is a hack/tip how I can best calculate data?
Currently I do have an excelfile with many many formulas and was wondering if I can "rebuild" them in Analyzer or better ETL?
Concrete example: There is one column filled with values between 1 and 10. I now want to know how the SUM of all 1s, 2s, 3s and so on is.
Then I want to build 3 new values: the SUM of 9&10, 7&8 and other values and with these results I want to do further calculations, like:
(SUM(9&10) minus (SUM all other values))/(SUM(9&10)+SUM(7&8)+SUM(all other values)) and best to get percent as result.
Answers

@Juliane You will want to do these in Analyzer if you're planning on doing any dynamic filtering or date filtering. In ETL, you won't be able to do aggregations and still maintain your raw data. In those cases, you will want to do aggregations in Analyzer.
When you want to sum things that meet certain conditions, you will want to use a CASE WHEN statement, which is like IF...THEN... :
SUM(case when 'Field' = 'Condition' then 1 else 0 end)  This would give you a count of every row that meets the condition.
SUM(case when 'Field' = 'Condition' then 'Field' else 0 end)  If your field is a number, then this would give you the sum of every row that meets this condition.
If you have multiple conditions for a field, you can use IN instead of equals:
'Field' IN(1,2,3) would look at every row that has 1,2, or 3.
So for your example of a sum of 1,2,3:
SUM(case when 'Field' IN(1,2,3) then 'Field' else 0 end)  Not sure if you're going for a SUM or COUNT but you can use examples above to modify as needed.
If you want to look at what doesn't meet a condition, then you could use the following:
SUM(case when 'Field' <> 'Condition' then 1 else 0 end)  Or for multiple conditions it would 'Field' NOT IN(1,2,3)
So in your sum columns and percent columns, you would just replace what's in your SUM statements with the case when statements that meet your conditions.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
2 
Hi Rob,
thank you very much for your help.
The Count() of the individual values did work very well  thanks for this.
I also tried to use the created fields/formular for the final calculation of (SUM(9&10) minus (SUM all other values))/(SUM(9&10)+SUM(7&8)+SUM(1,2,3,4,5,6)) and best to get percent as result but failed  it shows an empty column. Any tip/hint how this could work?
Thanks
Juliane
0 
For the final calculation, you could try something like:
(SUM(CASE WHEN `Field` IN ('9','10') THEN 1 ELSE 0 END)  Sum of 9 and 10  SUM(CASE WHEN `Field` IN ('9','10') THEN 0 ELSE 1 END))  Sum of all Other Values / COUNT(`Field`)
It's hard to be too sure without seeing more of the dataset, Could you post a few lines of the data?
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman2 
I have in this column all values between 1&10.
Not sure if it makes sense to first create three new columns with the COUNT ( 9&10), (7&8) and (all other values) and then do the final calculation?
When I create a COUNT column with
COUNT(case when `Did this workshop/work session meet your expectations?` = 9 then `Did this workshop/work session meet your expectations?` else 0 end), I get the following result:
How can I calculate with these rows instead of columns?
0
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 294 Workbench
 6 Cloud Amplifier
 8 Federated
 2.8K Transform
 97 SQL DataFlows
 607 Datasets
 2.1K Magic ETL
 3.8K Visualize
 2.4K Charting
 707 Beast Mode
 49 App Studio
 39 Variables
 667 Automate
 170 Apps
 446 APIs & Domo Developer
 44 Workflows
 7 DomoAI
 33 Predict
 13 Jupyter Workspaces
 20 R & Python Tiles
 391 Distribute
 111 Domo Everywhere
 274 Scheduled Reports
 6 Software Integrations
 115 Manage
 112 Governance & Security
 Domo Community Gallery
 31 Product Releases
 9 Domo University
 5.3K Community Forums
 40 Getting Started
 30 Community Member Introductions
 103 Community Announcements
 4.8K Archive