Creating a running total across rows with table calculations


#1

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) +
coalesce(pivot_offset(${order_items.count},-3),0)

Giving you these results!


Creating a Running Total Across Rows Redux: Using pivot_offset_list
#2

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.


#3

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


(Colin) #4

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.


Running total that subtracts subsequent values across rows
(Colin) #5

I’d add that this example may solve your needs in the meantime as we get the functions built:
https://learn.looker.com/explore/imdb_redshift/title_base?qid=zMujzJOVZxmBZq4uoq62vj

sum(
  pivot_offset_list(
    ${movie_budget.average_budget}
    , 1 - max(pivot_row(pivot_column()))
    , max(pivot_row(pivot_column()))
    )
)