Subtotals on row with calculated dimension

Looker Q&A Looker
1

Hi,

I have followed this guide (Guide) to be able to add subtotals in my table. The problem now is that i would like to add the subtotal on each row and not just the last for that category.

So i found this solution when using a week
max(offset_list(${total_calc}, 0, 7))
But this code retrives the largest number up to 7 rows below. This works when using weeks where i know its always 7 steps. In my table one category could contain different number of subrows.

Does anyone have an idea how to solve this?

Cheers
Daniel

2

Hi @Danvik ,

Thanks for your message! Can you clarify if you need the value from the last subtotal in the partition on each row of the partition or just adding their values, creating a running subtotal per partition like in this Discourse:

[Creating a running total down columns with table calculations](https://discourse.looker.com/t/creating-a-running-total-down-columns-with-table-calculations/1053) Table Calculations

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: [image] So to create a running total of the column Orders Count, write a table calculation like so: running_total(${orders.count}…

Best,

3

Hi

What i would like to have is something similar to this.

510debba4af42236fab065c84bacfeec0cc41271.png
510debba4af42236fab065c84bacfeec0cc41271.png488×149 1.77 KB

4

This is possible with table calculations. In the example below, we are using ${products.brand} and ${products.item} as dimensions, and ${orders.count} as measure.

First we need to find the group start row and end row:

group start row:

match(${products.brand}, ${products.brand})

next group start row:

count(${products.brand}) - match(${products.brand}, offset(${products.brand}, count(${products.brand}) - row() * 2 + 1)) + 2

Once we have these, we can get the sum per group (brand).

group sum:

sum(offset_list(${orders.count}, -1 * (row() - ${group_start_row}), ${next_group_start_row} - ${group_start_row}))

And then finally, for your percent of group total:

${orders.count}/${group_sum}
2 Likes
5

Hi Guys,

I’m really new to the looker environment. I would like to know how I can insert this solution above in this screen that I sent the print?

This solution is exactly something that I really need. I didn’t understand where it is possible to apply these lines of code.

I thank you for all your help.

6

Those examples are referring to table calculations (written in what we call Looker Expressions language) rather than LookML.

Check this out: https://docs.looker.com/exploring-data/using-table-calculations for a primer on how to use table calcs! They’re super useful, and more lightweight than LookML. Also for clarity, each one of those code blocks is representing a different table calc, so you’ll want to make 4 different table calculations for @brecht ‘s example.

1 Like