Question

Templated Pivots for Custom Aggregates

  • 28 August 2015
  • 3 replies
  • 253 views

When building an interactive Kaplan Meier dashboard for my organization, I wanted to allow users to choose to pivot on a number of a different dimension (i.e., select the variable to use for cohort comparison). Unfortunately, this seemed impossible because the choice of variable to pivot on has to happen in a subquery/CTE before a few CTEs worth of reasonably complicated SQL transformations are applied.



By hacking the templated filter feature, I was able to come up with a solution.



- view: KM

derived_table:

sql: |

with

accounts as (

SELECT

user_id,

CASE

WHEN NOT({% condition gender %} 'xxxxxxxxxx' {% endcondition %}) THEN gender

ELSE extract('year' from start_date)

end AS cohort,

MAX(case when created_at is not null

THEN 1

ELSE 0

END) AS event,

COALESCE(

MIN(GREATEST(TRUNC(date_part('day', created_at - start_date)/7),1)),

MAX(greatest(TRUNC(date_part('day', current_date - start_date)/7),1))

) AS duration

FROM timeline

group by

1,

2

),

.....







fields:

### TEMPLATED FILTERS ###

- filter: gender

label: 'GROUP BY Gender'

type: string

default_value: DONOTCHANGE



### Dimensions and measures ###



- dimension: pivot

description: 'If you want to use any of the pivots defined above, you must pivot on this field.'

type: string

sql: ${TABLE}.cohort



When the gender filter is selected, the SQL that’s generated by Looker becomes:



  WHEN NOT(( 'xxxxxxxxxx'  = 'DONOTCHANGE')) THEN gender

ELSE extract('year' from start_date)

END AS cohort,



And when the filter is not selected, the generated SQL is:



  WHEN NOT(1=1) THEN gender

ELSE extract('year' from start_date)

END AS cohort,



Which is exactly what we need!



This method should generalize to any custom aggregate function, providing you generalize your SQL enough. Good luck!


3 replies

Hi Michael - This example sounds like something I was looking for, but I’m having a hard time visualizing how the user would use this. Would you be able to post some screenshots of this being used when building a Look? Thanks!

Nevermind, makes sense now! This is great =)

For anyone who is interested, I’ve written up a blog post on how we used this here.

Reply