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