6 Replies Latest reply on Nov 12, 2015 11:59 AM by Shinichiro Murakami

    Double Counting Customers

    Amos Mbugua

      Hi,

       

      I am trying to present the # of customers per month broken down in to categories i.e. new-ordered for the 1st time that month and existing - has ordered previously.

       

      I have created the following calculated fields:

       

      First Order Date={ FIXED [Customer]:MIN([Date Order])}

      New or Existing = IIF([First Order Date]=[Date Order],'New','Existing')

      New customers = If ATTR([New or Existing])="New" THEN COUNTD([Customer]) END

       

      The problem that is arising is when I use the New or Existing measure,  it accurately counts new customers but will also count the new customer as existing if they order more than once.


      As per the attached workbook you will find that in Jan we had 10 new customers, but  since one new customer ordered more than once they have been counted as existing. In February the #s are correct as no new customer ordered more than once.


      I want a calculation that will accurately count existing customers without double counting them if they are new.


      I have attached a workbook.

        • 1. Re: Double Counting Customers
          Shinichiro Murakami

          Amos,

           

          Look like your "New' customer is new for that MONTH.

          In that sense, I put another calculation like below.

          The formula itself is way far from most excellent one, but I hope you can understand what I tried to do.

           

          New or Existing (1) =

          IIF((year([Date Order])*10000+month([Date Order]))>(year([First Order Date])*10000+month([First Order Date])),'New','Existing')

          Picture1.JPG

          Thanks,

          Shin

          • 2. Re: Double Counting Customers
            Amos Mbugua

            Shinichiro,

             

            The formula seems to work on this data set, may I ask why you have put the

            amount 10000?

             

            On Wed, Nov 11, 2015 at 3:36 PM, Shinichiro Murakami <

            • 3. Re: Double Counting Customers
              Carl Slifer

              Hello Amos.

               

              There are two ways we can handle this. See the changes to your formulas below are in red.

               

              Option 1

              First Order Date={ FIXED [Customer]:MIN([Date Order])}

              New or Existing = IIF([First Order Date]=[Date Order],'New','Existing')

              New customers = If [New or Existing]="New" THEN [Customer] END

               

              Then use the New Customer's Field and Change this to Count Distinct while it's inside the View

               

              Option 2 (Now With Less Formulas)

              First Order Date={ FIXED [Customer]:MIN([Date Order])}

              New or Existing = IIF([First Order Date]=[Date Order],[Customer],NULL) 

               

              The use the 'New or Existing' Field on the rows shelf and change the measure to count discrete.

               

              Carl Slifer

              InterWorks

              • 4. Re: Double Counting Customers
                Shinichiro Murakami

                The reason to put 10000.

                I am not very familiar to Date related formula, then I cheated.

                 

                ie) X=2015/11    ==> Year(X) brings 2015,  then month(X) brings 11. 

                To make X unique month, I put Year(X)*10000+month(X) as formula.

                Then the answer is 20150011.   I now realized without "day" 100 is enough......

                The answer in using 100's case, Year(X)*100+month(X) becomes 201511.

                If I include day,

                Y=2015/11/1 ==>   Year(Y)*10000+month(Y)*100+day(Y) = 20151101.

                 

                Help??

                 

                Sorry for the confusion.

                 

                One more thing,

                If you don't mind, could you check "correct answer' or "helpful answer" to keep my motivation high. 

                 

                Shin

                • 5. Re: Double Counting Customers
                  Amos Mbugua

                  Ok Shin,

                   

                  I think I get the logic. Thanks.

                   

                  On Thu, Nov 12, 2015 at 2:18 AM, Shinichiro Murakami <

                  • 6. Re: Double Counting Customers
                    Shinichiro Murakami

                    Sounds good.

                     

                    One more thing,

                    If you don't mind, could you check "correct answer' or "helpful answer" to keep my motivation high. 

                     

                    Thanks,

                    Shin