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:

3 Likes

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
RETIRED: 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?

0 Likes

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

0 Likes

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

2 Likes

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

0 Likes

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

1 Like

(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

0 Likes

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

1 Like

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

0 Likes

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

0 Likes

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

0 Likes

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

0 Likes

(Noa Flaherty) #13

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

Does anybody have any ideas?

1 Like

(Menashe Hamm) #14
0 Likes