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:
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_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

8 Replies
Contributor III
Author

Creator II

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!

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.

Partner - Champion

Hi @19iv1987 ,

Maybe this:

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

I hope it can helps.

Best Regards

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. 😞

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;

Contributor III
Author

Here my real data model

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 )

Community Browser