Solved: Running Total with Pivot - Page 2

Ski_Blanchard · 05-28-2019 01:38 PM

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.

DaanIF

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

View solution in original post