Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
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

Hi @Kashyap_R 

I really appreciate your support !!!

I have mentioned the business requirement in details in the beginning of the discussion.

Basically your formula resulting the customer who has not palced "B2B" order only for last month(selected month-1) by comparing the selected month B2B customers.

But unfortunately its not meeting the creteria of customer who placed Non- B2B order as their last order in past 36 months and who is not new to B2B type of order(as per the history of 36 months of data).

I really appreciate, if you can help me with this 

vinieme12
Champion III
Champion III

Why not create an Asof table in your datamodel ?

create an asOf table with LatestDate ORder date asOf that business date for each b2b customer

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
suryajeganathan
Contributor III
Contributor III
Author

@Kashyap_R 

I used the same formula as you mentioned above, but I am not getting the correct results like you. Can you please check and let me know where I am going wrong?

Note : I have a date column with (MM/DD/YYYY) 

Formula:

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

suryajeganathan_0-1649017694553.png

 

Kashyap_R
Partner - Specialist
Partner - Specialist

Hi

As you replied in message just change ur date format everywhere

$(=Date(Monthstart(max(Date),-12),'MM/DD/YYYY'))

 

I hope it helps 

Regards

kashyap.R

Thanks and Regards
Kashyap.R