Measure outside of the pivot

done
file
normal_priority

(Dawid) #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?


(Andrew Powell) #2

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


(Dawid) #3

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?


(Andrew Powell) #4

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


(Dawid) #5

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 :slight_smile: