Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
suryajeganathan
Contributor III
Contributor III

Set Analysis - Required Tweak in script

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

 

 

Labels (5)
13 Replies
suryajeganathan
Contributor III
Contributor III
Author

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

suryajeganathan
Contributor III
Contributor III
Author

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

suryajeganathan
Contributor III
Contributor III
Author

@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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
suryajeganathan
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

@Anil_Babu_Samineni - Can you please help me here?

I have simplified the question as you mentioned

 

Kashyap_R
Partner - Specialist
Partner - Specialist

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

kashyap_0-1648627376553.png

hope this helps you

 

Thanks and Regards

Kashyap.R

 

Thanks and Regards
Kashyap.R
suryajeganathan
Contributor III
Contributor III
Author

@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
Partner - Specialist
Partner - Specialist

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

Thanks and Regards
Kashyap.R