Total of two running total columns

done
low_priority
reply

#1

I have a look that has new users by month. It has a pivot on it to show verified vs not verified totals by month. I then added a running total so that I could see a cumulative user total by month. I turned on row totals and it totals the new users by month. But I want to also total the running totals by row so I have a cumulative total by month. I can’t figure out the table calculation to do this. Please help.


(Nicholas Wong) #2

The answer is simply, Table Calculations!

27%20AM

Table calculations enable you to perform calculation based on your SQL query results. Through this method, you’re creating a derived table which is the easiest way of querying compared to native derived table. You can perform complex calculation on either ephemeral or written into the database (Persistent Database Table – PDT).

To create running total, you can use the running_total() function.

In order to get the cumulative total of your users by month, you can have verification of users as your pivot and display the month on the side. This means you’ll get something like this:

58%20AM

Then all you need to do is to add in another table calculation for sum!


#3

Thanks but I didn’t know the table calculation to use. I used Looker Chat and the support person there helped me. Here’s the answer if anyone searches this in the future:

You need to use a pivot calculation:
pivot_index(${cumulative_total},1) + pivot_index(${cumulative_total},2) - this is since I had two pivot columns.


(Andrew Powell) #4

You might want to switch the syntax to this:

sum(pivot_row(${cumulative_total}))

As that will give you a more scalable syntax which will work against any number of pivot columns (not just two).


(Menashe Hamm) #5

I second @powellandy said, that sum(pivot_row( is better in this case. Even where sum(pivot_row( is not appropriate, I recommend pivot_where over pivot_index for most cases, since pivot_index gets ruined if the user reorders the columns. (Try it and see.)