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?

WORKAROUND:

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…

SELECT  ID, SUM(VALUE1 + VALUE2)
FROM    tableName
GROUP   BY ID
ID, SUM(VALUE1 + VALUE2)
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: