Solved

Subtotals on row with calculated dimension

  • 27 March 2018
  • 5 replies
  • 4777 views

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

icon

Best answer by brecht 3 April 2018, 12:22

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 original

5 replies

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.

Userlevel 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}

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.

Userlevel 7
Badge

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.

Reply