This attached report is supposed to be a Retention Report. Currently it shows cancelations because I'm having trouble creating a calculation to show percentage retained instead of # canceled.
The report is based on 2 tables. COHORTS and ORGS. They are related by Cohort ID which is assigned to every Org based on the date of their order. One COHORT > many ORGS.
Currently, the report shows the number of Orgs that canceled their service in ≤ the number of months at the top of each column. e.g. In Cohort 70, 4 organizations canceled their service within 3 months of ordering. I want it to show the percentage of orgs that have NOT canceled in each column.
The layout is based on COHORTS. Each cell in this report is a single row portal to ORGS based on the Cohort ID. A calc field (Orgs::MonthsToCancel) calculates the number of months between the Date Ordered and Date Canceled (if canceled).
A summary field in Orgs (s_CountCanceled) counts the number of records with a date in Orgs::DateCanceled.
Each portal is filtered to show values from ORGS::s_CountCanceled if the value in Orgs:MonthsToCancel is ≤ the number at the top of each column.
Can anyone tell me how to modify this to show the percentage of Retention (Total Orgs - Canceled) / Total Orgs? It should show a declining percentage over time. I’ve tried several combinations of Get Summary but no luck so far.