I am trying to create a cohort analysis with the % retention, month after month.
I have created a cohort table with absolute numbers, but I cannot calculate a percentage of the total for each cohort.
For example: I am trying to calculate the numbers within the pivot table as a percentOfTotal, where the total should be the distinctCount of created_at for each month.
Does anyone have any ideas ?
Thanks!
Hello,
Could you please share a small sample of “fake” data replicating your dataset for this table (10-12 rows).
If you can also include an screenshot to understand on each cell what is the meaning of each value? E.g. crated_at=May/Send_date=Jul2021, what is the total at that point?
Hi @Srdan_Jovanovic, distinct count is not currently supported with the percentOfTotal function in QuickSight. You can workaround this by creating your calculation in SQL.
I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
Peter is correct that percentOfTotal currently doesnt support distinct counts, however you could compute your own percent of total with your own numerator and denominator (where we will use an LAC-A function in the denominator to compute it at a different level).
distinct_count(field)/ sum(distinct_count(field, [created_at]))
Can you try that?
