Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
19iv1987
Contributor III
Contributor III

Advanced Set Analysis instead of aggr

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_VEHCILEYYYYMMYYYYMM_NUMBERYYYYMM_WARRANTYYYYYMM_WARRANTY_NUMBERDELTA
A20210117120190114526
A20201217020190114525
A20201116920190114524
A20201016820190114523
B20210117120200115714
B20201217020200115713
B20201116920200115712
C20210117120171213239
C20201217020171213238

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

 

8 Replies
19iv1987
Contributor III
Contributor III
Author

 
alex00321
Creator II
Creator II

I updated the formula to this one and it works:


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

alex00321_0-1616058934011.png

Let me know if this works for you. Thanks!

19iv1987
Contributor III
Contributor III
Author

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
Partner - Champion
Partner - Champion

Hi @19iv1987 ,

Maybe this:

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

I hope it can helps.

Best Regards

19iv1987
Contributor III
Contributor III
Author

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
Partner - Champion
Partner - Champion

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

Here my real data model

eddie_wagt
Partner - Creator III
Partner - Creator III

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 )