Solved: Subtotals on row with calculated dimension - Page 2

Danvik · 03-26-2018 10:54 PM

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

brecht

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