Fixed Functions along with Case statements

in the below data i require the sum of Value 2 when the sum of value 1 for the distinct values in the key column is greater than 0
the expected answer for the below will be 1+2+5+6 = 14
i can derive the SUM of value 1 by using the fixed function
SUM(SUM(`Value1`) FIXED(By `Key`))
However, when i wrap it inside a Case statement to SUM the Value 2 Colum where the Fixed sum of Value 1 is greater than 0 the evaluation fails
I have tried both the below variation , both fail at evaluation
Answers
-
Hi @vivekmani28 ,
So the trick is you need to have a column in your dataset that has like a 1 or blank for the rows you want. I made a video for you showing you how I got the 14 solution you were looking for: https://www.loom.com/share/cad8fea30bfc4d46bb81c2a75b639913
So make a column called Proxy with a 1 where you have value1>0 and use this formula:
CASE WHEN (SUM(SUM(`Value1`) FIXED(By `Key`)))>0 Then sum(SUM(`Value2`) FIXED(By `Proxy`)) END
Good luck to you,
John Le
You're only one dashboard away.
More video solutions at: https://www.dashboarddudes.com/pantry
John Le
Are you on my newsletter? If not, signup here so you don't miss out on my Domo tricks, alerts about my webinars, cooking tips and more
Signup here:
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 306 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 112 SQL DataFlows
- 649 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 788 Beast Mode
- 78 App Studio
- 43 Variables
- 745 Automate
- 187 Apps
- 475 APIs & Domo Developer
- 67 Workflows
- 16 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 406 Distribute
- 117 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 139 Manage
- 136 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive