How to show in percentages in retention cohort matrix

shaun · February 21, 2018, 7:48pm

How can we show numbers as percentages in a cohort (retention) analysis? I mean how to show each column as a % of 1st column?

shaun · February 21, 2018, 7:51pm

This should be possible but you have to do a few things:

You will not be able to use count-distinct. Use ndv which is an approximate to count distinct
Then, use window functions to grab the FIRST_VALUE for that row. The expression would look like this:

ndv([Order Id]) / first_value(ndv([Order Id])) over (partition by year([Order Date String]) order by month([Order Date String])) as 'percentage'

It will produce the following output.