Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

bruno_ml
New Contributor III

SET Analysis with Last Year

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

1 Solution

Accepted Solutions

Re: SET Analysis with Last Year

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

13 Replies
YoussefBelloum
Esteemed Contributor

Re: SET Analysis with Last Year

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

Re: SET Analysis with Last Year

TEST SET ANALYSIS.jpg

Re: SET Analysis with Last Year

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

Re: SET Analysis with Last Year

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

Re: SET Analysis with Last Year

Thanks it's better but it's not perfect

SET ANALYSIS Problem 2.jpg

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

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

Re: SET Analysis with Last Year

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

bruno_ml
New Contributor III

Re: SET Analysis with Last Year

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

Re: SET Analysis with Last Year

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

Community Browser