Inner-pivot total

  • 17 April 2019
  • 9 replies

Userlevel 5

Please don’t kill me for all the topics 😃

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?

9 replies

Userlevel 5


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.

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. 🙂

Userlevel 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

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. 🙂

Looker is based on SQL…

FROM tableName

1 7
2 16
Userlevel 5

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 🙂

Hi @Dawid_Nawrot, would it be very kind of you to tell me what does this WORKAROUND look like (as in what commands/functions are used). I have exactly two pivots and not dynamic. Thanks!

Userlevel 5

I think I had a calendar table with week_offset and since this report was looking at last 8 weeks I created measures like Week -8 Sum or something like that… can’t remember exactly.

Thank you.