4 Replies Latest reply on Feb 17, 2016 8:49 AM by sccardais

    Calculation in filtered portal

    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.

        • 1. Re: Calculation in filtered portal
          bigtom

          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.

          • 2. Re: Calculation in filtered portal
            sccardais

            bigtom

             

            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 IDUnique Serial #
            Cohort Datemm/01/yyyy
            c_count orgscount based on relationship matching on cohortID
            ORGS
            ORG IDUnique Serial Number
            Date OrderDate field.
            Date CancelWill be blank if Org has not canceled.
            Cohort IDLookup from Cohorts based on Order Date
            c_count cancelcalc. count if Cancel Date has any value
            s_CancelsSummary_Total c_count cancel
            c_Months to Cancelcalc. If date in Cancel Date, calculates the number of months between Order Date and Cancel Date
            • 4. Re: Calculation in filtered portal
              sccardais

              Beverly -

               

              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.

               

              SCC