Create columns based on other columns with filters applied

[bromy]

Hello,

I’m new on looker so please be understanding 😃

I’d like to create a dashboard representing a monthly ratio, which is basically for each user gender, count of A divided by (count of A + count of B). Please see my explore bellow.

Is this possible using Table Calculation or anything else -?

Thank you !

[Dawid]

If you only had one pivot you could do

sum(pivot_where(${signups_sender.user_gender} = “m”))

in order to get your total but unfortunately the pivots do not work in hierarchical way. If you have more than one pivot referencing only one of the contexts like the above yields nothing.

You need to be explicit. Here would be the code to get the ratio for the M

pivot_where(${signps_sender.user_gender} = “m” AND ${event.event_type} = “A”, ${event.count_distinct}) / 


(
    pivot_where(${signps_sender.user_gender} = “m” AND ${event.event_type} = “A”,    ${event.count_distinct}) 
    + 
    pivot_where(${signps_sender.user_gender} = “m” AND ${event.event_type} = “B”, ${event.count_distinct})

)

Though personally I would first create totals within each level 1 pivot, in case I wanted to do other calculations.

The above is a hacky and explicit way to get this ratio. The day we’ll get pivot functions to play nicely with multiple pivots will be a very happy day for many people!