3 Replies Latest reply on Aug 23, 2018 12:30 PM by Jim Dehner

Classifying customers as New vs. Existing

Hi Community,

So we recently launched a new product line and I'm trying to classify customers as New vs. Existing. A customer is existing if they have ordered from both the legacy and the new product line. A customer is new if they have ordered only form the new product line. Here is the calculated formula that I thought would give me what I want ----

IF { INCLUDE [Sold to Customer Account 1 Name1]: SUM(IF [Grouped Material]='New Product Line' THEN { INCLUDE [Grouped Material]: SUM([Confirmed Qty at Base UoM1])}END)}>0

AND {INCLUDE [Sold to Customer Account 1 Name1]: SUM(IF [Grouped Material]='Legacy Product Line' THEN {INCLUDE [Grouped Material]: SUM([Confirmed Qty at Base UoM1])}END)}>0

THEN

'Existing'

ELSEIF { INCLUDE [Sold to Customer Account 1 Name1]: SUM(IF [Grouped Material]='New Product Line' THEN { INCLUDE [Grouped Material]: SUM([Confirmed Qty at Base UoM1])}END)}>0

AND ISNULL({INCLUDE [Sold to Customer Account 1 Name1]: SUM(IF [Grouped Material]='Legacy Product Line' THEN {INCLUDE [Grouped Material]: SUM([Confirmed Qty at Base UoM1])}END)})

THEN

'New'

ELSE

NULL

END

---- However when I do this it keeps splitting the data as so...      Its splitting up the data by product line and I cant figure out how to consolidate it.

When I take out the grouped material row it still splits it but with different results as so...

The big takeaway is that if a customer has BOTH the new product line and the legacy it should be labeled as Existing. The measurement to determine this is confirmed quantity at base.

I have tried about 5 different calculations that include using both groups and sets so feel free to take at look at them for ideas as to how I've approached this.

I've attached a sample packaged workbook as well.

Thanks for any help on this,

Xander

• 1. Re: Classifying customers as New vs. Existing

I broke the problem into 3 steps

First

next

then this

it will return this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• 2. Re: Classifying customers as New vs. Existing

Thank you Jim, you are a true Jedi.

• 3. Re: Classifying customers as New vs. Existing

Thanks