Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to create a waterfall chart with multiple dimensions. To do that I will need to calculate an offset as in the table below. It needs the total of all the groups prior to the current group. 2016 needs 0, 2017 needs 2016, 2018 needs 2016 + 2017, etc.
If I can calculate that value I can use a conditional to zero out the value for all but the bottom Dept on the stack.
FY | Dept | Net Total | Offset |
$6,753,949 | 0 | ||
2016 | A | $935,468 | 0 |
2016 | B | $333,285 | 0 |
2016 | C | $691,286 | 0 |
2017 | A | $560,821 | 1,960,038 |
2017 | B | $910,090 | 1,960,038 |
2017 | C | $642,713 | 1,960,038 |
2018 | A | $414,761 | 4,073,662 |
2018 | B | $791,806 | 4,073,662 |
2018 | C | $555,142 | 4,073,662 |
2019 | A | $332,736 | 5,835,371 |
2019 | B | $580,395 | 5,835,371 |
2019 | C | $5,447 | 5,835,371 |
I've tried to use RangeSum
=RangeSum(Above(Sum([Net Total]),0,RowNo()-1))
but I'm getting just the sum of the current group and I can't figure out the correct set analysis.
Can someone please assist?
Do you have QV 12 or QV11? If you have QV11, you will have to fix the load order for Fiscal Year because it seems that the load order for Fiscal Year field is 2017, 2018, 2019, 2016. Once you fix this, the chart should fix itself. If you have QV12, you can make use of this The sortable Aggr function is finally here! and try something like this:
=If(Segment = MaxString(TOTAL <[Fiscal Year]>Segment), Max(TOTAL <[Fiscal Year]> Aggr(RangeSum(Above(Sum([Net Total]), 0, RowNo())), ([Fiscal Year], (NUMERIC, ASCENDING)))) - Max(TOTAL <[Fiscal Year]> Aggr(Sum([Net Total]), [Fiscal Year]))
,0)
I think you might need to use TOTAL Qualifier here... try this for Offset
Sum(TOTAL <FY> [Net Total])
See here to check out how TOTAL Qualifier works:
A colleague got me started with this formula for the offset.
=IF(Segment=MaxString(TOTAL <[Fiscal Year]>Segment),max(total <[Fiscal Year]> aggr(rangesum(above(sum([Net Total]),0,rowno())),[Fiscal Year]))
- max(total <[Fiscal Year]> aggr(sum([Net Total]),[Fiscal Year]))
,0)
It almost works but the first period is going above the last. How can I get the first period to start at 0?
Do you have QV 12 or QV11? If you have QV11, you will have to fix the load order for Fiscal Year because it seems that the load order for Fiscal Year field is 2017, 2018, 2019, 2016. Once you fix this, the chart should fix itself. If you have QV12, you can make use of this The sortable Aggr function is finally here! and try something like this:
=If(Segment = MaxString(TOTAL <[Fiscal Year]>Segment), Max(TOTAL <[Fiscal Year]> Aggr(RangeSum(Above(Sum([Net Total]), 0, RowNo())), ([Fiscal Year], (NUMERIC, ASCENDING)))) - Max(TOTAL <[Fiscal Year]> Aggr(Sum([Net Total]), [Fiscal Year]))
,0)
Wow. That looks like it fixed it. I'm in QV 11 bc of the prod environment.
I added an ORDER BY "Fiscal Year" to the sql statement in the script and it's sorting correctly now.
Thanks for the help!
Awesome