How can we show numbers as percentages in a cohort (retention) analysis? I mean how to show each column as a % of 1st column?
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.