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

    Classifying customers as New vs. Existing

    Xander Spears

      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.

       

         Capture.PNG                 

       

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

       

       

          

      Capture 2.PNG

       

      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