Question

Measure outside of the pivot

  • 4 March 2019
  • 4 replies
  • 1389 views

Userlevel 7
Badge +1

I hope the topic fits in the table calculations category.


To make the example simple imagine I have a table of number of sessions by URL and then I add a pivot for a boolean flag.


How do I create a measure that only shows once as a difference of the first from the second (or the other way)?


Pivot_offset would give me a custom measure, of course, but it will show twice in the result table with the first (or second) column empty.


Any ideas?


4 replies

Userlevel 3

Hi,


Yes table calculations seems appropriate here, although you could also easily achieve this in the model.


If you want to use a table calculation then look at the pivot_where function, https://docs.looker.com/exploring-data/creating-looker-expressions/pivot_where. You can use this to create one measure, rather than a measure per pivot.


The syntax would be

pivot_where(${dimension}=yes,${measure_1}) - pivot_where(${dimension}=no,${measure_2})


Andy

Userlevel 7
Badge +1

Oh! That’s an interesting approach. Thanks, I will give it a go and report back!


I checked and even by entering this simple formula:


pivot_where(${beforeafter}="1. After", ${measure})


it says:


The first argument for "pivot_where" must be a measure or a pivoted dimension


That before/after is custom dimension (string)


– another edit

When I saved it, it still worked but it was still showing the above error. Perhaps it has to do something with the fact that I used custom dimension here?

Userlevel 3

I’ve checked and I see the same error message, though you’re right that it does work in the end. It looks like its caused by the pivot dimension being a custom dimension rather than an actual dimension. Looks like a bug to me.


Andy

Userlevel 7
Badge +1

Yes, good to know that it’s sometimes good to click SAVE anyway to see if it works, in case error is a false positive 🙂

Reply