5 Replies Latest reply: Mar 22, 2017 4:20 PM by Azmina Virani RSS

    Compare Multiple years list

    Azmina Virani

      I have this data (included below).

       

      I want to find get a count of Customer_Account_ID that in the last 12 month that occurred more than 1 time. (Answer: 4 because customer Account ID 1, 4 and 8 only occurred once in the last 12 months )

       

      (this expression is not working):

      =(Count(Aggr(ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -12))"},

          CUSTOMER_ACCOUNT_ID = {"=Count(CUSTOMER_ACCOUNT_ID) >= 2"}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID)))  - I am not getting the right answer

       

       

      Then for all customers in the last 12 months that occurred one time - If they occurred at least one time in the last 2 years I want to get a sum.  (Answer is 2, because out of 1,4,8 - only 4 and 8 appeared in the last 36 months)

      =if(AGGR(ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -12))"},

          CUSTOMER_ACCOUNT_ID = {"=Count(CUSTOMER_ACCOUNT_ID) = 1"}>}distinct CUSTOMER_ACCOUNT_ID)

          =ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -36))<$(=AddMonths(MonthStart(Today()), 12))"}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID)

       

       

      This is to calculate all account with 1 transaction count:

      count  (AGGR(

          ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -12))"},

      CUSTOMER_ACCOUNT_ID = {"=Count(CUSTOMER_ACCOUNT_ID) = 1"}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID))

       

       

      =(Count(Aggr(ONLY({$<[Transaction Date] = {">=$(=AddMonths(MonthStart(Today()), -35))<$(=AddMonths(MonthStart(Today()), -11))"}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID)))

       

       

      The final answer would be 6.

      4 from two of more transaction from current year

      and

      2 or more from all transaction that had 1 transaction in the last 12 months but appeared at least one more time in the last 36 months. 

       

       

      Any guidance will help.

        

      Transaction DateCUSTOMER_ACCOUNT_ID
      1/1/201410
      2/1/20146
      3/1/20144
      4/1/20147
      5/1/20144
      6/1/20144
      7/1/20144
      8/1/20147
      9/1/20142
      10/1/20142
      11/1/20144
      12/1/20143
      1/1/20152
      2/1/20158
      3/1/20157
      4/1/20154
      5/1/20158
      6/1/20153
      7/1/20155
      8/1/20153
      9/1/20156
      10/1/20158
      11/1/20159
      12/1/20154
      1/1/20167
      2/1/201610
      3/1/20164
      4/1/20167
      5/1/20169
      6/1/20166
      7/1/20166
      8/1/20167
      9/1/20168
      10/1/20164
      11/1/201610
      12/1/201610
      1/1/20171
      2/1/201710
      3/1/20179
        • Re: Compare Multiple years list
          Nicole Smith

          Azmina Virani wrote:

           

          I want to find get a count of Customer_Account_ID that in the last 12 month that occurred more than 1 time. (Answer: 4 because customer Account ID 1, 4 and 8 only occurred once in the last 12 months )

           

           

          =count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>1'}>}distinct CUSTOMER_ACCOUNT_ID)

           

          Azmina Virani wrote:

           

          Then for all customers in the last 12 months that occurred one time - If they occurred at least one time in the last 2 years I want to get a sum.  (Answer is 2, because out of 1,4,8 - only 4 and 8 appeared in the last 36 months)

           

          =count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -35))"}>}[Transaction Date])=1'}>}distinct CUSTOMER_ACCOUNT_ID)

           

          I've also attached an example file.  Let me know if it fixes the issue.

            • Re: Compare Multiple years list
              Azmina Virani

              Almost -

               

              I still am not able to get the last part.

               

              =count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>1'}>}distinct CUSTOMER_ACCOUNT_ID)

               

              The expression above gives a distinct count of all Customer_Account_ID that had more than 1 transaction.

               

              The second part of - for all the transaction that that 1 transaction count - if the occur in the last 36 month as well - I want to add that count to the initial expression.

               

              Example: (I changed the date 1/1/2016 from 7 to 5.)

               

              There are 4 customers who did more that 1 transaction in the last 12 months. Customer 1, 4, 8 did only one.

              Now if customer 1,4 and 8 did any transaction between 13-36 month from now - I want add that customer to my original count of 4.

               

               

              Does this explain?

               

              Transaction Date

              CUSTOMER_ACCOUNT_ID
              1/1/201410
              2/1/20146
              3/1/20144
              4/1/20147
              5/1/20144
              6/1/20144
              7/1/20144
              8/1/20147
              9/1/20142
              10/1/20142
              11/1/20144
              12/1/20143
              1/1/20152
              2/1/20158
              3/1/20157
              4/1/20154
              5/1/20158
              6/1/20153
              7/1/20155
              8/1/20153
              9/1/20156
              10/1/20158
              11/1/20159
              12/1/20154
              1/1/20165
              2/1/201610
              3/1/20164
              4/1/20167
              5/1/20169
              6/1/20166
              7/1/20166
              8/1/20167
              9/1/20168
              10/1/20164
              11/1/201610
              12/1/201610
              1/1/20171
              2/1/201710
              3/1/20179
                • Re: Compare Multiple years list
                  Nicole Smith

                  Okay, I think I understand what you're asking now...

                   

                  I think the second part should be:

                  =count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])=1'}*{'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -35))<$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>0'}>}distinct CUSTOMER_ACCOUNT_ID)

                   

                  Then you would just add the above to what I gave you previously:

                  =count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>1'}>}distinct CUSTOMER_ACCOUNT_ID)

                • Re: Compare Multiple years list
                  Azmina Virani

                  Think I got it.

                   

                  RANGESUM(

                      count({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])>1'}>}distinct CUSTOMER_ACCOUNT_ID),

                  Count(if(AGGR(ONLY({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])=1'}>}distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID)

                  =  AGGR(ONLY({<CUSTOMER_ACCOUNT_ID={'=count({<[Transaction Date]={">=$(=AddMonths(MonthStart(Today()), -35))<$(=AddMonths(MonthStart(Today()), -11))"}>}[Transaction Date])> 2'}>}

                  distinct CUSTOMER_ACCOUNT_ID), CUSTOMER_ACCOUNT_ID),CUSTOMER_ACCOUNT_ID)))