It is no clear to me what you want to see where and which values are sums of what?
I am pretty sure what you want is easy enough to do , but just want to understand exactly what you are looking for. If you can mark up the image that might help.
Thanks for reply and any help. Marked up image is attached.
The report currently shows the number of Orgs that canceled in ≤ 3, 6, 12, etc months. I want this report to show % Retention.
So, the value for the column labeled <3 should be (49-4)/49 = 91.83%. The value for column labeled <6 should be (49-7)/49 = 85.71% etc.
I agree that it "should" be easy. I'm sure I'm just missing something simple.
RED BOX: The total number of orgs in the cohort. It's a calc field in COHORTS that counts OrgID in ORGS based on the TO that connects COHORTS to ORGS based on the CohortID
BLUE BOXES: filtered portals to Orgs based on match of cohort ID. One single cell portal per column. Portal is filtered to show records from ORGS if ORGS_Months to Cancel is ≤ the value in the column header. The field in each portal is ORGS::s_sum count of canceled. (Summary field Count of Date Canceled)
GREEN BOXES: just column labels. The values 3, 6, 9, etc are manually entered in each portals filtering criteria.
Thanks again for any help.
COHORTS Cohort ID Unique Serial # Cohort Date mm/01/yyyy c_count orgs count based on relationship matching on cohortID ORGS ORG ID Unique Serial Number Date Order Date field. Date Cancel Will be blank if Org has not canceled. Cohort ID Lookup from Cohorts based on Order Date c_count cancel calc. count if Cancel Date has any value s_Cancels Summary_Total c_count cancel c_Months to Cancel calc. If date in Cancel Date, calculates the number of months between Order Date and Cancel Date
Your article was the inspiration for the report I’m trying to create. Your article on Aggregates in Filtered Portals was a terrific help. I read everything you write and learn a lot from every article. Clearly, I don’t learn enough but keep them coming - please.