SET Analysis with Last Year

[bruno_ml]

Hello,

I have a expression with SET Analysis which I am not succeed to create.

My need is to have the Amount of sales of Real Customer (FICTIF_FLAG=0) who have buy last year minimum 2 type of products.

My Expression is here :

SUM(

{<

$(v_SET_Flag_FICTIF),$(v_SET_Y-1)

,[KEY_CUSTOMER]={'=count({<[FICTIF_FLAG]={0},[DATE]={'<=$(=Date(Addmonths(Max(DATE),-12),'DD/MM/YYYY')) >=$(=Date(Addmonths(Min(DATE),-12),'DD/MM/YYYY'))'},[DATE.autoCalendar.Date],[DATE.autoCalendar.Month],[DATE.autoCalendar.Quarter],[DATE.autoCalendar.Week],[DATE.autoCalendar.YearMonth],[DATE.autoCalendar.Year],[PRODUCT.AXIS]={'Make-up','Fragrances','Skincare'}>} distinct [PRODUCT.AXIS])>1'}

>} VALID_SALES/YTD_CHANGE_RATE)

Do you have an idea where is my fault ?

Kind regard,

Bruno

[YoussefBelloum]

Hi,

would be able to share something (image or sample data) which represent your data structure ? or maybe you're working on a test app with sample data that you would be comfortable sharing ?

[bruno_ml]

[sunny_talwar]

May be this

Sum({<$(v_SET_Flag_FICTIF),$(v_SET_Y-1),

[KEY_CUSTOMER] = {"=Count({<[FICTIF_FLAG]={0}, [DATE] ={[<=$(=Date(Addmonths(Max(DATE),-12),'DD/MM/YYYY'))>=$(=Date(Addmonths(Min(DATE),-12),'DD/MM/YYYY'))]},[DATE.autoCalendar.Date], [DATE.autoCalendar.Month], [DATE.autoCalendar.Quarter], [DATE.autoCalendar.Week],[DATE.autoCalendar.YearMonth], [DATE.autoCalendar.Year], [PRODUCT.AXIS] = {'Make-up','Fragrances','Skincare'}>} DISTINCT [PRODUCT.AXIS]) > 1"}>}

VALID_SALES/YTD_CHANGE_RATE)

[bruno_ml]

Hi,

Yes sure, you can see here a Cross Table by Year and Customer with count({<[FICTIF_FLAG]={0},[PRODUCT.AXIS]={'Make-up','Fragrances','Skincare'}>} distinct [PRODUCT.AXIS]).

We can see for this KEY_CUSTOMER, he have buy 3 products in 2017 ans 3 products too in 2016.

in the Cross table above, I have the Cross Sell for y-1,the second expression is

count({<[FICTIF_FLAG]={0},[DATE]={'<=$(=Date(Addmonths(Max(DATE),-12),'DD/MM/YYYY')) >=$(=Date(Addmonths(Min(DATE),-12),'DD/MM/YYYY'))'},[DATE.autoCalendar.Date],[DATE.autoCalendar.Month],[DATE.autoCalendar.Quarter],[DATE.autoCalendar.Week],[DATE.autoCalendar.YearMonth],[DATE.autoCalendar.Year],[PRODUCT.AXIS]={'Make-up','Fragrances','Skincare'}>} distinct [PRODUCT.AXIS]) to know how many product type I have buy in Y-1 and after we have the same but for the current year.

[bruno_ml]

Thanks it's better but it's not perfect

I have a result but it's not the result for the LastYear (1492.8) but only 638.4.

For my experience, what is the difference which you have correct please ?

[sunny_talwar · Accepted solution]

Changed these things

Sum({<$(v_SET_Flag_FICTIF),$(v_SET_Y-1),

[KEY_CUSTOMER] = {"=Count({<[FICTIF_FLAG]={0}, [DATE] ={[<=$(=Date(Addmonths(Max(DATE),-12),'DD/MM/YYYY'))>=$(=Date(Addmonths(Min(DATE),-12),'DD/MM/YYYY'))]},[DATE.autoCalendar.Date], [DATE.autoCalendar.Month], [DATE.autoCalendar.Quarter], [DATE.autoCalendar.Week],[DATE.autoCalendar.YearMonth], [DATE.autoCalendar.Year], [PRODUCT.AXIS] = {'Make-up','Fragrances','Skincare'}>} DISTINCT [PRODUCT.AXIS]) > 1"}>}

VALID_SALES/YTD_CHANGE_RATE)

Basically, you cannot use single quotes within single quotes within single quotes without using Escape sequences‌. Alternate is to use double quotes and/or square brackets

[sunny_talwar]

What is v_SET_Flag_FICTIF and v_SET_Y-1? Can you share what the definition is?

[bruno_ml]

Yes :

v_SET_Flag_FICTIF

[FICTIF_FLAG]={0}

v_SET_Y-1

[DATE]={"<=$(=Date(Addmonths(Max(DATE),-12),'$(v_SET_Format_Date)')) >=$(=Date(Addmonths(Min(DATE),-12),'$(v_SET_Format_Date)'))"}   ,[DATE.autoCalendar.Date],[DATE.autoCalendar.Month],[DATE.autoCalendar.Quarter], [DATE.autoCalendar.Week],[DATE.autoCalendar.YearMonth],[DATE.autoCalendar.Year]

You can find here the datas for this exemple

[bruno_ml]

I have do my Test, it's because I use Min and Max of Date and for 2017 he have buy between 13/02/2017 and 02/12/2017 but in 2016 in the same period the amount is 638.4 only.

Thnaks a lot for your help !!