Creating a running total across rows with table calculations

  • 14 September 2015
  • 6 replies

Userlevel 5

You can create a running total across rows with table calculations using the pivot_offset function.

You can also create a running total down columns with table calcs using the running_total function. Read more about this here.

To create a running total across rows, use the pivot_offset function to add each value to the previous values in that row, while using coalesce() on each previous value to ensure it is not NULL.

For example, if you have this query:

You can write this table calculation:

${order_items.count} + 
coalesce(pivot_offset(${order_items.count},-1),0) +
coalesce(pivot_offset(${order_items.count},-2),0) +

Giving you these results!

6 replies

Am I understanding correctly that we need to manually add each column to a calculation like this? This seems like a really clumsy and inefficient way to calculate this. I hate that I need to bang my head against the wall in order to perform what could otherwise be done pretty effortlessly with a SQL window function.

What is the point of forcing users into a pivot-centric paradigm without providing them with the tools to navigate said paradigm? Baffling.

Thanks for the feedback. You are right on this one, we have some catch-up to do on pivot-list functions as compared to our columnar functions. Working on adding pivot_running_total and pivot_running_product to the next release or two.

I’d add that this example may solve your needs in the meantime as we get the functions built:

, 1 - max(pivot_row(pivot_column()))
, max(pivot_row(pivot_column()))

Any progress in launching these functions?

Userlevel 2

Here’s another option that returns the same result