Creating a running total down columns with table calculations


#1

Table calculations enable you to create calculations that operate on the data table returned by a SQL query. This is great for calculating metrics like running totals.

You can also create a running total across rows using table cals. Read more about how to do this here.

To create a running total using table calculations, you can use the running_total() function:

So to create a running total of the column Orders Count, write a table calculation like so:

running_total(${orders.count})

This will look something like:


Creating a running total across rows with table calculations
Cumulative Count for every day in a month
Creating a column percent of total with table calculations
Table Calculations (3.18 Labs Feature)
Subtotals on row with calculated dimension
(Dave Kim) #2

Is it possible to have the running total be nested if there are more than one dimension? For example, if your data included a ‘Month’ column before the date would you be able to perform a running total by month?


#3

This isn’t currently possible with table calculations, but would be definitely possible using window functions in the SQL (which I realize is not as flexible). I’ll pass this on to our product team!


#4

Actually Dave, I thought about this a little more, and I think something similar to what you’re looking for is possible with pivots. If you pivot on month, you can create a running_total that cumulates each month by the day of the month. In this case, there would be one column per month.

This would look something like:


(Abhinav Suraiya) #5

Hi Lindsey!

This is a really cool feature, but I’m curious whether the running total can be limited to a certain number of rows. My use case is that I have a table showing weekly counts, and I want to also be able to see a running “last 4 weeks” count. I’m considering doing this via a running_total, but limiting it to the 4 rows immediately below the current row. Is that possible? if not, is there another way to achieve what I’m looking for?
Thanks!


#6

Hey Abhinav - I think the best way to do something like this would be to use the offset function, which allows you to refer to a row X rows away from the current one. So in your case, in order to get a 4 week running total, you could create a table calc like this:

offset(${orders.count},-3) + 
offset(${orders.count},-2) + 
offset(${orders.count},-1) + 
${orders.count}

This would sum the current row and the three rows preceeding it, resulting in a trailing 4 week total.


(Ken Yeoh) #7

Is there a way to reverse the order of the running total (bottom to top, or right to left)?

I have some calculation that is “Days left in the month on the X axis” which is an integer, but trying to reverse the Axis order in the visualization to “Ordinal” crashes Chrome (but not Firefox).

Edit:
Managed to get this working by calculating the running total on top of a negative number and multiplying that by -1 in table calcs


(Colin) #8

@kyeohhubspot This is definitely possible with offset_list. It’s on the roadmap to incorporate more easily, but at the moment you can use this example from Learn.


(Michelle Ogburn) #9

Wondering if there is a way to do a table calculation on running total but with a distinct count. My use case is I need to count clients that receive services each month. Often times clients may receive services in more than one month. I don’t want to count them two or three times.

I’ve also looked at doing a table calculation on the minimum Service Date but really didn’t get anywhere with that. Don’t seem to be able to calculate min/max dates.


(Kiran) #10

Is there any way to display the sum total at end of all the rows (instead of adding running total as a column at the end?). Once i add my table calculation, i see that the “Totals” at the bottom of the grid are not being displayed correctly. Appreciate your help here.


(romain.ducarrouge) #11

Hi @kmahamk, in the example listed at the very top, if you wanted to get the total for Orders Count, you could tick the ‘Totals’ box and would be able to even reference it in table calculations this way: ${orders.count:total}

If you wish to get the total for the table calculation column the same fashion, it is not available currently in Looker, but I would pass the feedback along to our product folks for you!


(Kiran) #12

Thank you very much for the update. I’m trying to get the total for the table calculation column. I would appreciate if you can pass on this feedback so that this can be included may be in a future release.


(Noa Flaherty) #13

I am trying to do the same. Were you ever able to find a solution?

Does anybody have any ideas?


(Menashe Hamm) #14

@davekim Subtotals with Table Calculations