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!