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]

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]

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

[suryajeganathan]

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

[Kashyap_R]

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