Solved: Running Total with Pivot

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

izzymiller

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!

DaanIF