Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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.
Hi @19iv1987 ,
Maybe this:
Count( {< YYYYMM, DELTA = {">=0<=36"} >} DISTINCT ID_VEHCILE )
I hope it can helps.
Best Regards
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. 😞
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;
Here my real data model
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 )