AnsweredAssumed Answered

Calculation in filtered portal

Question asked by sccardais on Feb 16, 2016
Latest reply on Feb 17, 2016 by sccardais

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.