5 Replies Latest reply on May 7, 2017 10:47 AM by Prayson Wilfred Daniel

    Help identifying new accounts, lost accounts, and renewed accounts between two years

    donny.gore

      Essentially what I'm trying to do is identify

      • Accounts that exist in 2015, but not 2016, to label them as 'New'
      • Accounts that exist in 2016, but not 2015, to label them as 'Lost'
      • Accounts that exist in both 2015 and 2016 to label them as 'Renewed'

       

      Ultimately, I'm needing to show a summary of something like

      New: 25

      Lost: 30

      Renewed: 100

       

      The route I thought would work would be to create a calculation for each year and then calculate the label from that, but that didn't work out so well...

       

      I feel like I'm just overlooking something obvious, but I just can't figure out how to get what I'm trying to do. Sample workbook attached. Appreciate anyone taking the time to take a look.

       

      Thanks!

        • 1. Re: Help identifying new accounts, lost accounts, and renewed accounts between two years
          Joe Oppelt

          I'm doing exactly that now.

           

          I'm not going to do it all for you, but I'll share some of the calcs I'm using.

           

          //[Constituent dropped this period?]

          if (isnull([Is this constituent active by the end of selected period])

              or [Is this constituent active by the end of selected period] = 0)

          and [Is this constituent active before this period] >=1 then 1 else 0 END

           

           

          //[Is this constituent active by the end of selected period]

          { FIXED [ConstituentDefinitionRollupId]
          //  ,[PledgeId]  // -- If we add in PledgeID, we will see if a specific pledge is new,
                           //  But the question is whether the *constituent* is new.  If he already has
                           // an existing pledge, and adds a new one, or if he has multiple pledges and
                           // drops one and leaves others in place, we don't want counts changing.  So just do this
                           // at the constituent level.  If we ever want to do multiple pledge analysis,
                           // then add in the PledgeID level both here and in the "prior" calc.
          :
          MAX(if [StartDate] <= [Analysis End date]
          and (isnull([CancelDate]) or [CancelDate] >= [Analysis End date])
          and (isnull([EndDate]) or [EndDate] >= [Analysis End date])

          // so this says, is the constituent active on the selected day.

          then 1 end) }

           

           

          //[Is this constituent active before this period]

          { FIXED [ConstituentDefinitionRollupId]
          //  ,[PledgeId]  // -- If we add in PledgeID, we will see if a specific pledge is new,
                           //  But the question is whether the *constituent* is new.  If he already has
                           // an existing pledge, and adds a new one, or if he has multiple pledges and
                           // drops one and leaves others in place, we don't want counts changing.  So just do this
                           // at the constituent level.  If we ever want to do multiple pledge analysis,
                           // then add in the PledgeID level.
          :
          MAX(if [StartDate] <= [Analysis Start date]-1
          and (isnull([CancelDate]) or [CancelDate] >= [Analysis Start date]-1)
          and (isnull([EndDate]) or [EndDate] >= [Analysis Start date]-1)

          // so this says, is the constituent active on the prior day.

          then 1 end) }

           

           

          You will have your own ways to know who started and stopped, I'm sure.

           

          Oh, you'll need some way to know when you start looking and when you stop looking.

           

           

          //[Analysis Start Date]

          if [Requested View] = "Most Recent" then [Max start date in data]
          elseif [Requested View] = "Specific Day" then [End Analysis Period]
          elseif [Requested View] = "Range of Days" then
              //  don't allow "backwards" date ranges
            IF [Start Analysis Period] > [End Analysis Period] then
             [End Analysis Period] else [Start Analysis Period]
            end

          END

           

          Note:  in this calc ^^ all the fields are parameters except for [Max Start Date in data].  We give the user options for ranges of analysis.  (Current day;  Specific day (and another parameter to select that day); Range of days (and two parameters to specify start and stop dates.).)

           

          //[Analysis End date]

          if [Requested View] = "Most Recent" then [Max start date in data]
          elseif [Requested View] = "Specific Day" then [End Analysis Period]
          elseif [Requested View] = "Range of Days" then
           
             [End Analysis Period]

          END

           

          Same deal with parameters in this calc ^^.

          1 of 1 people found this helpful
          • 2. Re: Help identifying new accounts, lost accounts, and renewed accounts between two years
            Prayson Wilfred Daniel

            I am working on a similar project that has to do with retention, upsell, etc. Thus I have to identify lost customers, returned customer, new customers and retained customers

             

            Your logic is very simple: Simply do this:

             

            {FIXED [Account]: MAX(IIF(

                    ({FIXED [Account]: MAX([Year])= '2015'}),'New',

                        IIF({FIXED [Account]:MIN([Year])}='2016','Lost','Renewed')))}

             

             

            As you can see it follows your logic, namely:

            //Accounts that exist in 2015, but not 2016, to label them as 'New'

            //Accounts that exist in 2016, but not 2015, to label them as 'Lost'

            //Accounts that exist in both 2015 and 2016 to label them as 'Renewed'

             

            To get your summary: Place  Status Core on Columns and COUNT(Status Columns) on Text in Marks pane.

             

            What the expression does.

             

            {FIXED [Account]: MAX(IIF(

                    ({FIXED [Account]: MAX([Year])= '2015'}),'New',

                        IIF({FIXED [Account]:MIN([Year])}='2016','Lost','Renewed')))}

             

            It fixed our results on the Account level of detail, and check: if the account has '2015' as MAX. This means that that account has only 2015, but not 2016. Since if it had, then 2016 would be MaX

             

            if the account has '2015' as MAX then .'New' This means that that account has only 2015, but not 2016. Since if it had, then 2016 would be MaX

             

            else

            if the account has '2016' as MIN, meaning only 2016, but not 2015 (Since if it had 2015 then 2015 would be MIN) then 'Lost',

            else 'Renewed'

             

            Hope that helped.

            2 of 2 people found this helpful
            • 3. Re: Help identifying new accounts, lost accounts, and renewed accounts between two years
              donny.gore

              Thanks! It did indeed help :)

               

              Thanks for also walking me through the logic in the calculation - that was extremely helpful.

              • 5. Re: Help identifying new accounts, lost accounts, and renewed accounts between two years
                Prayson Wilfred Daniel

                We could have also use Sets to perform this task. Say we have customers in set A(2015)and B(2016).  Inner join are Renewed, Left, not including intersection are Lost, Right, not including intersection are New.

                 

                see: Examples of Sets