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

# Double Counting Customers

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

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')

Thanks,

Shin

• ###### 2. Re: Double Counting Customers

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

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

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

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

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