Two Group By with SUM totals

PJG

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!

nmizzell

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:

rco

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).

nmizzell

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:

rco

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).

PJG

Thanks again, @rco! Changing -1 to 0, and removing the Correct for Dummy tile did it!