Two Group By with SUM totals
As always, terrible title by me I'm sure :)
This is quite similar to a question I had last week that @rco helped me with, and I'm using his answer as the basis for what I'm working on, but am stuck on getting it to work.
Last week I was doing counts of projects, but this week, I need to SUM the project cost. Simplified Input to Output mockup:
I'm not sure if I can actually use the same approach, but this is what I have. I've made adjustments to switch from count to SUM, and to the Planned Cost column, but I think the fake project ID and the correct for Dummy don't work here?
My output layout is at least correct, note that the column labeled Planned Cost is really a Total of the adjacent rows, but the values are missing or null:
Let me know if I can share any tile configs to help troubleshoot.
Thanks!
Best Answers
-
Hey PJG,
Here is what you will need to do:
this simple 2 step proccess will transform the input dataset provided into the output dataset.
I truncated the column names to make it easier for me to put this together.
Here are the truncated names that I used in my dataset:
0 -
You should be able to take the solution we did for Project Count with these changes:
1. Rename "ProjectId Const" tile to "Cost Const", name the constant "Planned Cost" instead of "Project Id", and make it zero instead of negative one.
2. In the Group By, do a Sum of "Planned Cost" instead of a Count Distinct of "Project Id".
3. Delete the "Correct for Dummy" tile, it isn't necessary (since our "dummy" is a cost of zero, which doesn't affect the sum at all so it doesn't need to be corrected).Randall Oveson <randall.oveson@domo.com>
1
Answers
-
Hey PJG,
Here is what you will need to do:
this simple 2 step proccess will transform the input dataset provided into the output dataset.
I truncated the column names to make it easier for me to put this together.
Here are the truncated names that I used in my dataset:
0 -
You should be able to take the solution we did for Project Count with these changes:
1. Rename "ProjectId Const" tile to "Cost Const", name the constant "Planned Cost" instead of "Project Id", and make it zero instead of negative one.
2. In the Group By, do a Sum of "Planned Cost" instead of a Count Distinct of "Project Id".
3. Delete the "Correct for Dummy" tile, it isn't necessary (since our "dummy" is a cost of zero, which doesn't affect the sum at all so it doesn't need to be corrected).Randall Oveson <randall.oveson@domo.com>
1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 114 SQL DataFlows
- 654 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 797 Beast Mode
- 78 App Studio
- 44 Variables
- 757 Automate
- 188 Apps
- 480 APIs & Domo Developer
- 72 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 408 Distribute
- 119 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive