Subtotals on row with calculated dimension

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

Solved Solved
0 5 4,787
1 ACCEPTED SOLUTION

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}

View solution in original post

5 REPLIES 5

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:

Best,

Hi

What i would like to have is something similar to this.
510debba4af42236fab065c84bacfeec0cc41271.png

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}

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?

 

674c6946-ba6b-49f4-a91a-42e6bdc63c73.png



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.

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.

Top Labels in this Space
Top Solution Authors