Inner-pivot total

Please don’t kill me for all the topics :smiley:

I have the following table:

      |      week 1      |      week 2     |      week 3     |
      | type 1 | type 2  | type 1 | type 2 | type 1 | type 2 |

How do I calculate total for the week pivot? My aim is this:

      |          week 1          |          week 2         |          week 3         |
      | type 1 | type 2  | total | type 1 | type 2 | total | type 1 | type 2 | total |

As far as I understand pivot_row would give me all values for each type and each week. Also I can’t use pivot_index as I’m trying to leave it dynamic so that people can change the number of weeks they want to see.

In other words I’m trying to figure out a way to ask the following question, let’s say I am in Week 1 and Type 1: (let’s assume I’m talking about number of sessions as measure)

What is the sum of "current" sessions + (sessions where pivot[week] = current and pivot[type] = '<this value can be hardcoded>')

The problem is also that I can’t reference the current value of pivots in functions, in order to use pivot_where.

After careful deliberation I came up with a more generic question that hopefully some people find easier to answer: How can we apply certain pivot calculations to a specific pivot level without hardcoding the values?


If the inner pivot doesn’t have too many columns, two in my case, you can create custom measures and then separate table calculations for each measure.

1 Like

Have you tried using pivot_where?

If I were you, I would create a custom measure to calculate the total for the week.

Pivot the week and add type 1 measure, type 2 measure and total measure.

This is by far the easiest way to go about building your dashboard. :slight_smile:

1 Like

@nicholaswongsg that’s the workaround I used, which is fine, I guess, but also it’s because I only have tow inner pivots. Imagine having 10 or more, then it becomes a mahoosive job

Hmm… I beg to differ though… Because if sum of type 1, 2, 3 or more is important… We should just create a measure for it. So if currently we have sum for type 1 and type 2 column - sum(column 1+ column2) - all we need to do is to add in column 3, column 4 and so on. :slight_smile:

Looker is based on SQL…

FROM    tableName
1   7
2   16

If i have 10 types I don’t want to create 10 different measures and then 10 x <number of calcs) I need. If I had to do it then I’d rather go and write SQL from scratch :slight_smile: