topic Re: SET Analysis with Last Year in App Development

SET Analysis with Last Year

bruno_ml — Fri, 27 Apr 2018 16:01:09 GMT

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

Re: SET Analysis with Last Year

YoussefBelloum — Fri, 27 Apr 2018 16:19:22 GMT

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 ?

Re: SET Analysis with Last Year

bruno_ml — Fri, 27 Apr 2018 16:22:39 GMT

Re: SET Analysis with Last Year

sunny_talwar — Fri, 27 Apr 2018 16:26:00 GMT

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)

Re: SET Analysis with Last Year

bruno_ml — Fri, 27 Apr 2018 16:26:53 GMT

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.

Re: SET Analysis with Last Year

bruno_ml — Fri, 27 Apr 2018 16:32:11 GMT

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 ?

Re: SET Analysis with Last Year

sunny_talwar — Fri, 27 Apr 2018 16:35:05 GMT

Changed these things

Sum({<$(v_SET_Flag_FICTIF),$(v_SET_Y-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

Re: SET Analysis with Last Year

sunny_talwar — Fri, 27 Apr 2018 16:36:50 GMT

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

Re: SET Analysis with Last Year

bruno_ml — Fri, 27 Apr 2018 16:41:02 GMT

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

Re: SET Analysis with Last Year

bruno_ml — Fri, 27 Apr 2018 16:44:50 GMT

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

Re: SET Analysis with Last Year

sunny_talwar — Fri, 27 Apr 2018 16:47:12 GMT

Can you check what you get when you use this in a KPI object?

='<=' & Date(Addmonths(Max(DATE),-12), '$(v_SET_Format_Date)') & '>=' & Date(Addmonths(Min(DATE),-12), '$(v_SET_Format_Date)')

Re: SET Analysis with Last Year

sunny_talwar — Fri, 27 Apr 2018 16:50:05 GMT

In that case, try this

[DATE] = {">=$(=Date(YearStart(Min(DATE), -1), '$(v_SET_Format_Date)'))<$(=Date(YearStart(Max(DATE)), '$(v_SET_Format_Date)'))"}, [DATE.autoCalendar.Date], [DATE.autoCalendar.Month], [DATE.autoCalendar.Quarter],

[DATE.autoCalendar.Week], [DATE.autoCalendar.YearMonth], [DATE.autoCalendar.Year]

Re: SET Analysis with Last Year

bruno_ml — Fri, 27 Apr 2018 16:51:29 GMT

I Have <=16/03/2017>=11/02/2013

Re: SET Analysis with Last Year

bruno_ml — Fri, 27 Apr 2018 16:52:43 GMT

Thanks for this, it's work for all year, I need to see if my users want since the begin of the year or for the same period exactly.