community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More

Create Customer for Lost/New customers

Meteoroid

Hi,

 

I have a dataset (attached) containing 40,000 rows. Below is the sample for quick reference. Not all customers will have sales in all the weeks.

I want to create a 4th column (named customer_Flag) with values as "New" or "Lost or Blank. 

Here are the conditions of new & Lost

    • New – Sales in the most recent 4 weeks and no sales in in weeks 5-30. ( Sum of Weeks 1- 4 > 0 and Sum of Weeks 5 -30 = 0)
    • Lost – no sales in the most recent 13 weeks. sales in weeks 14- 39. ( Sum of Weeks 1 -13 = 0 and Sum of Weeks 14 – 39 > 0)

Can someone please help me with a solution in Alteryx

Customer IDWeek_numSales
T0000000126
T00000001530
T00000001930
T000000011270
T000000011360
T000000011630
T000000012466
T000000012818
T000000012918
Alteryx Certified Partner
Alteryx Certified Partner

@agoyal28,

 

I am not able to understand the requirements here.  The data provided has a single entry for each Customer ID.  I expected that there would be Sales data for multiple period for any given customer.  When 1-4 >0 and 5-30 = 0, then New and 1-13=0 and 14-39 > 0 then Lost (else blank?).

 

Since a given customer can only exist in a single period, then everything would be blank.

 

Can you please clarify the ask?

 

Thanks,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteoroid

Apologies for the issue. i think i masked the original customer IDs in a wrong way. I have corrected them now in the attached file

 

T0000000126
T00000001530
T00000001930
T000000011270
T000000011360
T000000011630
T000000012466
T000000012818
T000000012918
T000000013118
Alteryx Certified Partner
Alteryx Certified Partner

@agoyal28,

 

I went binary with this problem.  For each record, I asked if it was true for each of the 4 date ranges (1-5, 1-13, 5-30 and 14-39).  I summarized the results using a MAX function so that I knew the true or false results for each group.  I could then answer the question for all transaction that a customer had with:

 

IF 
     [Max_1-4_Ind] and ![Max_5-30_Ind] THEN "New"	ELSEIF
     ![Max_1-13_Ind] and [Max_14-39_Ind] THEN "Lost"
ELSE
     ""
ENDIF

Please take a look at the workflow and see if it makes sense to you.

 

capture.jpg

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels