Currently, I have a running total going for each fiscal quarter, but what Iโm looking for is the running total across all fiscal quarters while maintaining the pivot for a quarter over quarter analysis. Is this possible?
For example, if Q1 ends at 41M I want Q2 to start at 41M and aggregate from there instead of starting back at 0. I would also need this same behavior for the upcoming quarters.
Solved! Go to Solution.
Hi Ski and Izzy,
Thanks for tagging me there, @izzymiller, haha! This is indeed another funny exercise.
I think I have found an other solution that would only need one Table Calculation.
This WILL require to turn on the Totals in the Data.
We will sum all Totals from previous columns and just add the running_total of the current column to them.
running_total(${ascent})
+
coalesce(
sum(
pivot_offset_list(
${ascent:total}
,1-pivot_column()
,pivot_column()-1
)
)
,0
)
I sincerely hope this helps your case, @Ski_Blanchard!
Kind regards,
Daan
Edit:
I came to think of that you can also skip the Totals option if you really donโt want it, by replacing ${ascent:total} by:
sum(offset_list(${ascent},1-row(),max(row())))
Ooh this is hard. Iโm bad at these kinds of table calcs, but @DaanIF schooled me last time so maybe he knows a way.
Like in most of these cases, this is fairly easy to do manually once you know the pattern, but really hard to do dynamically.
I stopped mine after 3 columns out of laziness, but hereโs an example:
the calc for that is:
if(pivot_column()=1,running_total(${ascent.count})
,
if(pivot_column()=2,
running_total(${ascent.count})+pivot_index(max(running_total(${ascent.count})),1),
if(pivot_column()=3,
running_total(${ascent.count})+
pivot_index(max(running_total(${ascent.count})),2)+pivot_index(max(running_total(${ascent.count})),1),null)))
So a nested if statement that takes the running total and adds the running total from the previous columns to it. My field is called ${ascent.count}, but it should work with yours too. Youโll need to add more nested if()'s if you have more columns!
Thereโs no concept of for loops in table calculations, though, so you canโt iterate over an unknown index. If anyone has a dynamic way, Iโm all ears!
Hi Ski and Izzy,
Thanks for tagging me there, @izzymiller, haha! This is indeed another funny exercise.
I think I have found an other solution that would only need one Table Calculation.
This WILL require to turn on the Totals in the Data.
We will sum all Totals from previous columns and just add the running_total of the current column to them.
running_total(${ascent})
+
coalesce(
sum(
pivot_offset_list(
${ascent:total}
,1-pivot_column()
,pivot_column()-1
)
)
,0
)
I sincerely hope this helps your case, @Ski_Blanchard!
Kind regards,
Daan
Edit:
I came to think of that you can also skip the Totals option if you really donโt want it, by replacing ${ascent:total} by:
sum(offset_list(${ascent},1-row(),max(row())))
Much clever-er ๐