AnsweredAssumed Answered

Help With Relationship to Count Related Records

Question asked by sccardais on Jul 8, 2016
Latest reply on Jul 8, 2016 by sccardais

I'm trying to create a Customer Retention report and having trouble creating a relationship that will allow me to count records that match my criteria.

 

TABLES

 

  • Table::YEARS holds starting and ending dates and additional fields to hold counts of customers at the beginning and end of each year. This table currently has 7 records starting in 2010.

 

  • Table::ORGs holds all customer information including Date Ordered and, if applicable, the Date Canceled.

 

RETENTION REPORT

 

Each row in the Retention report shows the Retention Rate for a specific Year.

 

The Retention formula requires three values:

    • Count of Customers at the Start of the Year
      • Orgs that ordered before the Start_Date and didn't cancel before the Start_Date. (Problem getting this.)
    • Count of Customers at the End of the Year
      • Orgs that ordered before the End_Date and didn't cancel before the End_Date. (Problem getting this.)
    • Count of Customers Added during the Year
      • Orgs that ordered in the date range for each year. (Not a problem.)

 

I've tried to get these counts using relationships between YEARS and ORGS but can't find a way to exclude organizations that canceled their service before the Starting period. To summarize:

 

  • The Count of Orgs at the start of each year should be the number of Orgs where the Order Date is before the Start Date minus the number of Orgs where the Date Canceled (if any) is also before the Start Date.

 

  • The Count of Orgs at the End of each year should be the number of Orgs where the Order Date is before the End Date minus the number of Orgs where the Date Canceled (if any) is also before the End Date.

 

Is there a way to do this via relationships?

Outcomes