Why is my grand total a negative?

kim_barragan0126 · January 28

I am trying to find out why the total is -$379,041. If you add up Week Ending Dates for the Test 1 Forecast, it should equal $710,766. What am I missing?

kim_barragan0126 · January 28

The issue is I had to use a join in ETL of add a forecast, which I am calling Direct Hire GP. And the $363,269 populated on each line, so I just to show the $363,269 one time.

DavidChurchman · January 29

Okay, I think this is what you'd want for your Test 1 Forecast beastmode:

SUM(AVG(`Test`) FIXED(BY Week Ending Date)) - SUM(`Test 1`)

That first half sums the average of Test, fixed by week. I use the average so we get 363K once, and then fix it by week so that it uses the average for each week, summing across those weeks.

ColemenWilson · January 28

You've redacted the values fields. How are they aggregating? Are they beast modes? Tell us more

DavidChurchman · January 28

Coleman is right that you'll need to share more about your BeastModes to give a full answer, but what what appears to be happening is that you are subtracting the two columns. The first column is unchanging and the second isn't. The resulting grand total is the difference between the unchanging value and the sum of the changing values 363K - (148K+194K+219K+182K)=-379K.

My guess would be the first column is an average and the second is a sum. Or it might be the first is not fixed by week and the second is fixed by week. You need to adjust the first part of your Test 1 Forecast BeastMode to be able to aggregate across the weeks. I would start with that part and test it until you get 363K *4 instead of 363K. Then add in the part of the BeastMode where you subtract the other part, as that appears to already be working.

kim_barragan0126 · January 28

Thank you for the comments. Below are my columns. I am assuming the issue is that my first column is a Sum/distinct. Is there a way I can get an accurate total?

First Column

Third Column

DavidChurchman · January 28

SUM(DISTINCT) is only adding up the distinct values. In this case, all the values are the same, so it just returns 363K, where you want it to return 363K*4 for the four weeks.

Depending on why you are doing a SUM(DISTINCT) rather than a SUM(), you might be able to resolve this by just doing a SUM(). Or you might need a SUM(AVG() FIXED(BY ) ) if there is a reason you need to keep it DISTINCT, where the BY is the Week or whatever category defines how you need the sum to aggregate.

Here's the KB on FIXED functions: https://domo-support.domo.com/s/article/4408174643607?language=en_US

kim_barragan0126 · January 28

The issue is I had to use a join in ETL of add a forecast, which I am calling Direct Hire GP. And the $363,269 populated on each line, so I just to show the $363,269 one time.

DavidChurchman · January 29

Okay, I think this is what you'd want for your Test 1 Forecast beastmode:

SUM(AVG(`Test`) FIXED(BY Week Ending Date)) - SUM(`Test 1`)

That first half sums the average of Test, fixed by week. I use the average so we get 363K once, and then fix it by week so that it uses the average for each week, summing across those weeks.

kim_barragan0126 · January 29

Thank you!

Why is my grand total a negative?

Best Answers

Answers

Categories