4 Replies Latest reply: Aug 22, 2016 4:15 PM by Azmina Virani RSS

    Distinct count of customers in last 3 months

    Azmina Virani
      Rolling 3 month customers
      Current Data
      DateCustomerID
      12/15/20151
      12/13/20152
      12/1/20152
      12/12/20154
      1/11/20151
      1/15/20153
      1/1/20154
      2/14/20155
      2/17/20153
      2/16/20151
      2/1/20152
      3/17/20155
      3/21/20151
      3/14/20153
      3/11/20154
      WANT
      Feb-151-- CustomerID 1 made a transaction on Feb, Jan and Dec
      Mar-152-- Cust ID 1 and 3 made a transaction on March, Feb and Jan

      Basically want the distinct count of repeat customers on the last 3 months

        • Re: Distinct count of customers in last 3 months
          Sunny Talwar

          Do you need a front end solution or can a little back end scripting to create flags can be done here?

            • Re: Distinct count of customers in last 3 months
              Azmina Virani

              I am ok with either solution. Front end or back end, whichever gets me the correct solution. Thanks!

                • Re: Distinct count of customers in last 3 months
                  Sunny Talwar

                  Try this script:

                   

                  Table:

                  LOAD *,

                    MonthName(Date) as MonthYear

                  INLINE [

                      Date, CustomerID

                      12/15/2014, 1

                      12/13/2014, 2

                      12/1/2014, 2

                      12/12/2014, 4

                      1/11/2015, 1

                      1/15/2015, 3

                      1/1/2015, 4

                      2/14/2015, 5

                      2/17/2015, 3

                      2/16/2015, 1

                      2/1/2015, 2

                      3/17/2015, 5

                      3/21/2015, 1

                      3/14/2015, 3

                      3/11/2015, 4

                  ];

                   

                  FinalTable:

                  LOAD *,

                  If(AddMonths(Previous(Previous(MonthYear)), 2) = MonthYear and AddMonths(Previous(MonthYear), 1) = MonthYear and

                      CustomerID = Previous(Previous(CustomerID)) and CustomerID = Previous(CustomerID), 1, 0) as Flag;

                  LOAD DISTINCT

                    MonthYear,

                    CustomerID

                  Resident Table

                  Order By CustomerID, MonthYear;

                   

                  On the front end create a straight table with MonthYear as dimension and Sum(Flag) as Expression

                   

                  Capture.PNG

                    • Re: Distinct count of customers in last 3 months
                      Azmina Virani

                      Thanks Sunny, Appreciate the help,

                      I have a similar problem, would you be able to help me alter the answer to only count last 3 months not including this month. For example:

                       

                       

                      Table:

                      LOAD *,

                        MonthName(Date) as MonthYear

                      INLINE [

                          Date, CustomerID

                          11/5/2014,3

                          11/25/2014,2

                          11/7/2014,1

                          11/30/2014,4

                          12/15/2014, 1

                          12/13/2014, 2

                          12/1/2014, 2

                          12/12/2014, 4

                          1/11/2015, 1

                          1/15/2015, 3

                          1/1/2015, 4

                          2/14/2015, 5

                          2/17/2015, 3

                          2/16/2015, 1

                          2/1/2015, 2

                          3/17/2015, 5

                          3/21/2015, 1

                          3/14/2015, 3

                          3/11/2015, 4

                          4/5/2015,2

                          4/15/2015,5

                          4/1/2015,1

                          4/28/2015,3

                      ];

                       

                       

                      WANT:

                      April 2015 - 2

                      March 2015- 1

                      Feb 2015- 2

                      Jan 2015 - 0

                      Dec 2015 - 0

                      Nov 2015 - 0

                       

                      I have created a new thread for it, I have attached the post to you.

                      Distinct Customers last 3 months rolling

                       

                      Thanks for the help!