Question

Creating a running total across rows with table calculations

  • 14 September 2015
  • 13 replies
  • 3484 views

Userlevel 5
Badge

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!



13 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 6
Badge +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.

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

 

 

Userlevel 6
Badge +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.

 

 

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

Reply