How to calculate cumulative totals on a measure in a pivot table?

[ihayes]

Is it possible to define cumulative measures in a table? Or at least have a pivot table show cumulative values

The spreadsheet mockup shown below illustrates what I am seeking

I want a ‘cumulative’ measure that can report all the values up to and including the time period in the column, including periods not in the report scope. i.e. before Jan

The ‘running total’ measure type is not the solution here, as it only seems to work for columns in the report display. Hence, if I changed the pivot table to show just Feb to April then with running total the ‘Acme’ row would not appear

In an OLAP tool this type of problem is pretty straightforward. I’d create a ‘Value’ dimension and have two members: ‘current month’ and ‘cumulative’, where the later would perform a sum on the related time dimension. But that type of cross-view calculation is a big hole for Looker.

Any suggestions?

[izzymiller]

ihayes:

as it only seems to work for columns in the report display. Hence, if I changed the pivot table to show just Feb to April then with running total the ‘Acme’ row would not appear

If you need your running totals to be irrespective of the values actually returned in the report, then you’ll need to pre-calculate them, since Looker (unlike an OLAP tool, where things are pre-analyzed) runs most calculations at query runtime on the raw data in your database.

That means you’ll want to use a derived table, which let you write arbitrary SQL and use the result as the basis for a Looker view. In this case, you’d want to precalculate the running total for each Client at each month, probably using a window function like the generic one below.

SELECT client, 
month, 
SUM(number) OVER (PARTITION BY client ORDER BY month) AS running_number_total
FROM table

Once you have the running totals pre-calculated in that derived table, you can build a view file on top of that, join it into your explore and then slice and dice your report all you like without the numbers changing.

[jason987]

I don’t buy the `Looker can’t do this because it doesn’t pre-analyse the data` argument. I implemented this in MSOLAP using a ROLAP connection which runs live queries against the underlying db without analysing any data. Tableau can also do this using a `Live` connection against the db.