Advanced Set Analysis instead of aggr

[19iv1987]

Hi All!

I'm facing a problem using advanced set analysis.

I found out a solution using aggr but due to the huge amount of data in my app the calculation engaged to much resources causing timeout problems so I must use a different type of calculation to achieved my result.

below my dataset:

ID_VEHCILE	YYYYMM	YYYYMM_NUMBER	YYYYMM_WARRANTY	YYYYMM_WARRANTY_NUMBER	DELTA
A	202101	171	201901	145	26
A	202012	170	201901	145	25
A	202011	169	201901	145	24
A	202010	168	201901	145	23
B	202101	171	202001	157	14
B	202012	170	202001	157	13
B	202011	169	202001	157	12
C	202101	171	201712	132	39
C	202012	170	201712	132	38

I should create a table using dimension YYYYMM and as KPI counting the number of Vehicle for each YYYYMM which satisfied the condition YYYYMM_NUMBER-YYYYMM_WARRANTY_NUMBER between 0 and 36.

Consider that an YYYYMM is always selected!

Using advanced set analysis as: 

Count({<YYYYMM=,ID_VEHCILE={"=(YYYYMM_NUMBER-YYYYMM_WARRANTY_NUMBER)<=36"}>*<YYYYMM=,ID_VEHCILE={"=(YYYYMM_NUMBER-YYYYMM_WARRANTY_NUMBER)>=0"}>}
DISTINCT ID_VEHCILE)

Qlik consider YYYYMM_NUMBER always equal to the value associated to the YYYYMM selected while it should be the value associated with the YYYYMM in the row!

I attach an example

Thanks in advance!!!!!

Eva

[alex00321]

I updated the formula to this one and it works:

Count({<YYYYMM=,DELTA={">=0<=36"}>}
ID_VEHCILE)

Let me know if this works for you. Thanks!

[19iv1987]

I forgot to say that in my real app YYYYMM_NUMBER and YYYYMM_WARRANTY_NUMBER are in two different tables and I cannot compute the field delta in ETL.

[agigliotti]

Hi @19iv1987 ,

Maybe this:

Count( {< YYYYMM, DELTA = {">=0<=36"} >} DISTINCT ID_VEHCILE )

I hope it can helps.

Best Regards

[19iv1987]

Hi Agigliotti, as I said in my real app YYYYMM_NUMBER and YYYYMM_WARRANTY_NUMBER are in two different tables and I cannot compute the field delta in ETL. 😞

[agigliotti]

could you share the real app data model?

or you can try this:

Count( {<  KEY0 = {"=(YYYYMM_NUMBER-YYYYMM_WARRANTY_NUMBER)>=0 and (YYYYMM_NUMBER-YYYYMM_WARRANTY_NUMBER)<=36"} > } DISTINCT KEY0 )

where KEY0 is ( ID_VEHCILE & YYYYMM ) in your script;

[19iv1987]

Here my real data model

[eddie_wagt]

Maybe if you calculate the delta on the fly? 

Count( {< YYYYMM, ID_VEHCILE = {"=(max(YYYYMM_NUMBER)-max(YYYYMM_WARRANTY_NUMBER))<=36"}, ID_VEHCILE = {"=(max(YYYYMM_NUMBER)-max(YYYYMM_WARRANTY_NUMBER))>=0"} >} DISTINCT ID_VEHCILE )