Question

# Creating a running total across rows with table calculations

• 15 replies
• 8119 views

Userlevel 5
• 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!

### 15 replies

Userlevel 3

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.

Userlevel 3

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

Userlevel 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.

Userlevel 4

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?

Userlevel 3

Here’s another option that returns the same result

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

@ross what window function can help solve this particular case where we want to get the pivot running sum?

Userlevel 7
+1

Is it crucial for you to do it with Table Calculations?

If, only if, you have a situation where you use a specific measure across pivots very often and need that running total across the row I create a measure, sometimes hidden.

``measure: orders_running_total_by_row {    type: running_total    direction: "row"    sql: \${orders.count}}``

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

This seems confusing, can we have comments on how this works and not just trying to memorize it?

@colin

Is it crucial for you to do it with Table Calculations?

If, only if, you have a situation where you use a specific measure across pivots very often and need that running total across the row I create a measure, sometimes hidden.

``measure: orders_running_total_by_row {    type: running_total    direction: "row"    sql: \${orders.count}}``

Oh...Interesting, there is a type called running_total?

Not sure I have used the direction key before.

Userlevel 7
+1

I use it very often. but rarely with direction: “row” but it’s good to know that such functionality exists. Here is more information: https://docs.looker.com/reference/field-reference/measure-type-reference#running_total

Pivot_offset_list creates an array.  Pivot_row() and pivot_column() are an index of where in the table you are.

In English, you are looking for: sum(everything to the left of me).  This can be explained as sum(column where I am back to column 1) or sum(list from column 1 to my column).  In calculation terms that means you need to start from N-1 columns back and include N columns.  The pivot_column sections are doing the math to get you back to column one from your column and then get you back to the current column.

To understand this more clearly it can help to decompose each piece into a separate calc and see what they are doing under the overall function.  It’s something you get more comfortable with after using Excel for a while.  Hope that helps.

Userlevel 1

Another way to get rolling total across rows without null values, by Ryan Bouquet:

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

Is there a way to reverse this? Essentially, sum everything to the left of me? I’m looking at the last 12 months and I want the most recent month to be the farthest left and then I want a running total of the row starting from the right.

Thanks!

Hi,

It seems I have a similar issue to what everyone here is dealing with, only that I need to calculate the count distinct as a row total up until a certain point for every column (attached picture):

and let me explain using the following example:

1. the first measurement column refers to the actual active (did a any action) users in a certain month (for instance, on January 22 there were 4,183 active users with a 0 (zero) delta months from the activation month, which represent a retention of 36.6% out of the total users that were activated during January 22.
2. let’s continue for the next month delta column at the same row:
on February 22 there were1,654 active users with a 1 (one) delta months from the activation month, which represent a retention of 14.5% out of the total users that were activated during January 22.
now let’s look on the row running total column: it sums up the total active users, which causing counting the same user more than once. actually, the row total function deals with that correctly:

what would be the right command to use in the table calculation section?

Thanks!!

Tal