Creating a running total across rows with table calculations

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!

3 15 11.5K
15 REPLIES 15

ross2
Participant III

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.

ross2
Participant III

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

Kehinde
Participant II

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

Dawid
Participant V

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}
}

Kehinde
Participant II

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?

@colin2 

Kehinde
Participant II

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.

I am new to Looker, will research more about this @Dawid .

Dawid
Participant V

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.

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

JJB
Observer

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

f5b6cc5e-189d-4ace-8d3e-4ea9d2a030ef.png

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:
    c0b2efee-b47e-471c-a213-652e76a0f2b7.png

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

Thanks!!

Tal

Top Labels in this Space
Top Solution Authors