Set Analysis - Required Tweak in script

[suryajeganathan]

Hi

I am trying to find a Recovered B2B customer. If an order placed in a month (any month in last 36) before the current month selected and that there was no B2B order, and it is not the 1st time the customer has placed B2B order, than then the customer should appear as recovered customer.

Can you please help me to tweak this script?

Set Analysis Script:

//last B2B order by customer -----
if(

COUNT({<
SalesChannel ={"B2B"},VolumeTypeCode = {"01"},
[Date] ={">=$(=Date((AddMonths(Max([Date]),-36)),'DD/MM/YYYY'))<=$(=Date(AddMonths(monthend(Max([Date])),-1),'DD/MM/YYYY'))"},
VolumeHL = {">0"},Revenue={">0"} ,Year,MonthCode,Month>}DISTINCT ShipToCode)

-

//new B2B customer
COUNT({<
SalesChannel={"B2B"},
[Date] = {">=$(=Date(MonthStart(Max([Date])),'DD/MM/YYYY'))<=$(=Max([Date]))"},
VolumeTypeCode = {"01"},
ShipToCode -= P({1<SalesChannel={"B2B"},VolumeTypeCode = {"01"},Year,Month,MonthCode,
[Date] ={">=$(=Date(YearStart(AddMonths(Max([Date]),-12)),'DD/MM/YYYY'))<=$(=Date(AddMonths(monthend(Max([Date])),-1),'DD/MM/YYYY'))"},
VolumeHL = {">0"}>} ShipToCode),
VolumeHL = {">0"},
Year,Month,MonthCode>} DISTINCT ShipToCode)>0,

///current month selection with B2B order
COUNT({<
SalesChannel={"B2B"},VolumeTypeCode = {"01"},Year,Month,MonthCode,
[Date] = {">=$(=Date(MonthStart(Max([Date])),'DD/MM/YYYY'))<=$(=Max([Date]))"},
VolumeHL = {">0"},Revenue = {">0"}>} distinct ShipToCode ))

[suryajeganathan]

@Taoufiq_Zarra /@sunny_talwar - Can you please help me here to fix the set analysis script ?

[suryajeganathan]

@Anil_Babu_Samineni Can you please help me here to fix the set analysis script ?

[suryajeganathan]

@swuehl

Hi

I am trying to find a Recovered B2B customer. If an order placed in a month (any month in last 36) before the current month selected and that there was no B2B order, and it is not the 1st time the customer has placed B2B order, than then the customer should appear as recovered customer.

Can you please help me to tweak this script?

Set Analysis Script:

//last B2B order by customer -----
if(

COUNT({<
SalesChannel ={"B2B"},VolumeTypeCode = {"01"},
[Date] ={">=$(=Date((AddMonths(Max([Date]),-36)),'DD/MM/YYYY'))<=$(=Date(AddMonths(monthend(Max([Date])),-1),'DD/MM/YYYY'))"},
VolumeHL = {">0"},Revenue={">0"} ,Year,MonthCode,Month>}DISTINCT ShipToCode)

-

//new B2B customer
COUNT({<
SalesChannel={"B2B"},
[Date] = {">=$(=Date(MonthStart(Max([Date])),'DD/MM/YYYY'))<=$(=Max([Date]))"},
VolumeTypeCode = {"01"},
ShipToCode -= P({1<SalesChannel={"B2B"},VolumeTypeCode = {"01"},Year,Month,MonthCode,
[Date] ={">=$(=Date(YearStart(AddMonths(Max([Date]),-12)),'DD/MM/YYYY'))<=$(=Date(AddMonths(monthend(Max([Date])),-1),'DD/MM/YYYY'))"},
VolumeHL = {">0"}>} ShipToCode),
VolumeHL = {">0"},
Year,Month,MonthCode>} DISTINCT ShipToCode)>0,

///current month selection with B2B order
COUNT({<
SalesChannel={"B2B"},VolumeTypeCode = {"01"},Year,Month,MonthCode,
[Date] = {">=$(=Date(MonthStart(Max([Date])),'DD/MM/YYYY'))<=$(=Max([Date]))"},
VolumeHL = {">0"},Revenue = {">0"}>} distinct ShipToCode ))

[Anil_Babu_Samineni]

Hi,

It is good if you can start with a dataset and expected results instead of your project needs. So we can corelate the same and you can replicate the same. Will you be able to do that?

[suryajeganathan]

Sure @Anil_Babu_Samineni will do that going forward.

In simple words, I am looking to fetch the last Non- B2B purchase of the customer.

Logic : Selected month -1 which is last order should not be B2B (In last 36 months)

I wrote the below mentioned formula , But its giving a result of customer with & without B2B order .

COUNT({<
SalesChannel -={"B2B"},VolumeTypeCode = {"01"},
[Date] ={">=$(=Date((AddMonths(Max([Date]),-36)),'DD/MM/YYYY'))<=$(=Date(AddMonths(monthend(Max([Date])),-1),'DD/MM/YYYY'))"},
VolumeHL = {">0"},Revenue={">0"} ,Year,MonthCode,Month>}DISTINCT ShipToCode)

Attached is the sample data

[suryajeganathan]

@Anil_Babu_Samineni - Can you please help me here?

I have simplified the question as you mentioned

[Kashyap_R]

Hi 

I took the sample data set and i did it foe last 12 months

I used the below expression i have take ship to as a customer u can replace with ur requred fields.

count(
Aggr(IF(Aggr(FirstSortedValue({<DD={">=$(=Monthstart(max(Date),-12))<=$(=Monthend(max(Date),-1))"}>}[Sales Channel],-DD),[Ship To])<>'B2B',
FirstSortedValue({<DD={">=$(=Monthstart(max(Date),-12))<=$(=Monthend(max(Date),-1))"}>}[Sales Channel],-DD)),[Ship To]))

You cant restrict the b2b in the set analysis itself because u want the latest transaction so get the latest trasaction type for each customer wrt last 36 months then check if it is b2b or not if yes take that as a count else dont

do some changes with expression above according to ur need

attached an image so u can get the output

hope this helps you

Thanks and Regards

Kashyap.R

[suryajeganathan]

@Kashyap_R Thank you so much for the response.

The script looks good.

But this script has to consider the customer with a B2B order in the selected month and the last order of the customer should n't be equal to B2B. 

For example : selected month is Feb'2022 and the customer(COLOMA INSTITUUT) placed B2B order  & the last order is on Oct'2021 which is not B2B then count "1". Can you please help me with this ?  

Filter Selection : Feb'2022
Sales Organisation	Year	Month	Ship To Code	Ship To	Sales Channel	# of Orders	Net Revenue	Volume (HL) (Invoiced)	Ship To Segment	Ship To Channel
BE11	2020	AUG	29015623	COLOMA INSTITUUT	B2B	1	286.32	7.56	Institutional	On Trade
BE11	2020	MAR	29015623	COLOMA INSTITUUT	B2B	1	286.32	7.56	Institutional	On Trade
BE11	2020	OCT	29015623	COLOMA INSTITUUT	B2B	1	286.32	7.56	Institutional	On Trade
BE11	2021	FEB	29015623	COLOMA INSTITUUT	B2B	1	286.32	7.56	Institutional	On Trade
BE11	2021	JUN	29015623	COLOMA INSTITUUT	B2B	1	286.32	7.56	Institutional	On Trade
BE11	2021	MAY	29015623	COLOMA INSTITUUT	B2B	1	286.32	7.56	Institutional	On Trade
BE11	2021	OCT	29015623	COLOMA INSTITUUT	CX SFDC	1	286.32	7.56	Institutional	On Trade
BE11	2022	FEB	29015623	COLOMA INSTITUUT	B2B	1	286.32	7.56	Institutional	On Trade

[Kashyap_R]

Hi @suryajeganathan 

Hope you are doing fine,

Kindly specify the requirements when you are posting the question so that they can give u the reply in single go and you will get the answer to your question asap.

Hope this expression helps and have an nice day.

count(

IF(Aggr(FirstSortedValue({<DD={">=$(=Monthstart(max(Date)))<=$(=(max(Date)))"}>}[Sales Channel],-DD),[Ship To])='B2B',

Aggr(IF(Aggr(FirstSortedValue({<DD={">=$(=Monthstart(max(Date),-12))<=$(=Monthend(max(Date),-1))"}>}[Sales Channel],-DD),[Ship To])<>'B2B',
FirstSortedValue({<DD={">=$(=Monthstart(max(Date),-12))<=$(=Monthend(max(Date),-1))"}>}[Sales Channel],-DD)),[Ship To])))

Thanks and Regards

Kashyap.R