Question

# Creating a running total across rows with table calculations

• 6 replies
• 22342 views

• Looker Staff
• 72 replies

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!

### 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:

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

Any progress in launching these functions?

Here’s another option that returns the same result

``````sum(pivot_offset_list(\${measure},-1*pivot_column()+1,pivot_column()))
``````