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.

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).

@Jesse tried with a similar setup as reported by @Srdan_Jovanovic, but received this error: â€śNesting of aggregate functions like / and COUNT(DISTINCT â€śActive Users (Last 90 Days)â€ť) / NULLIF(SUM(COUNT(DISTINCT â€śActive Users (Last 90 Days)â€ť)), 0) is not allowed.â€ť

For background, here is what I am working with:

Active Users (Last 90 Days): ifelse(dateDiff({completion_date},now()) <=90,{user_id},NULL)

This calculated field aggregate is set to Count Distinct and it works well! When trying to calculate the number of active users over the total sum of users to return the percent of total during the same 90 day period, this is where I am getting the above error:

Hi @jtuten - That error message is saying you cannot layer two aggregations on top of on another (e.g. sum of disctinct count). However when we write LAC calculations, the result is an un-aggregated field from quicksightâ€™s perspective. In your denominator inside the distinct_count you are missing the comma and the field you want to group that by, which turns it into an LAC.

distinct_count(field , [created_at])

If you dont want to group by anything (analagous to a subtotal) and want the total across all the data, you can put those square brackets but leave it empty inside