Inner-pivot total

(Dawid) #1

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?


(Dawid) #2


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

(Kyle) #3

Have you tried using pivot_where?


(Nicholas Wong) #4

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

(Dawid) #5

@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


(Nicholas Wong) #6

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