# Total of two running total columns

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

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:

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