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
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive