Two Group By with SUM totals

PJG
PJG Member
in Magic ETL

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:

image.png

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?

image.png

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:

image.png

Let me know if I can share any tile configs to help troubleshoot.

Thanks!

Best Answers

  • nmizzell
    nmizzell Contributor
    Answer ✓

    Hey PJG,

    Here is what you will need to do:

    image.png
    image.png
    image.png

    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:

    image.png

  • rco
    rco Domo Employee
    Answer ✓

    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>

Answers

  • nmizzell
    nmizzell Contributor
    Answer ✓

    Hey PJG,

    Here is what you will need to do:

    image.png
    image.png
    image.png

    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:

    image.png

  • rco
    rco Domo Employee
    Answer ✓

    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>

  • PJG
    PJG Member

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

Categories