Retention Rate cohort analysis

[xyz27]

I want to create a cohort analysis for retention rate.
I have 3 fields, activation_date, cancel_date and usernumber.

For this analysis, I have considered user’s who activated the service in Q1 2024 and what is their retention until 08/2024.

And I want output similar to below in Quicksight.

Column 1	Column 2	Column 3	Column 4	E	F	G	H	I
	1	2	3	4	5	6	7	8
24-Jan	1000	900	800	700	600	500	400	300
24-Feb		899	766	765	750	600	400	300
24-Mar			700	600	500	456	400	370

I need calculated fields that would help me achieve this goal.

[Nico]

Hi @xyz27 ,

welcome to the community

Do I get this right? Your visual shows all users that activated in Q1. The activation month is in the first column.

And as an example 1000 of the users that activated in Jan also canceled in Jan?

Can you offer us some kind of example data and store it in Amazon Arena?

NEW - QuickSight Arena -- An embedded instance of QuickSight within the QuickSight Community

[FeatureTile] QuickSight Arena provides you with an embedded instance of QuickSight right within the QuickSight Community. Using Arena, you can: Publish dashboards to the Community Gallery to showcase your skills Include dashboards depicting your use case while posting questions in the Community Q&A Clone underlying analysis views of published dashboards in order to learn / try answering questions Include dashboards, with working solutions, while responding to questions In this article, …

I created an example on Amazon Arena:
PivotTable

If this is what you want, there is no need for a calcualtion. Only a Pivot Table and the aggreagtion to Month.

Best regards,
Nico

[xyz27]

No, 1000 joined in January.
In Feb, there are 900 so 100 cancelled the service. So, basically we retained 900 out of 1000 in Feb and so on.