topic Re: Advanced Set Analysis instead of aggr in QlikView

Advanced Set Analysis instead of aggr

19iv1987 — Thu, 18 Mar 2021 08:28:31 GMT

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

Re: Advanced Set Analysis instead of aggr

19iv1987 — Thu, 18 Mar 2021 08:29:05 GMT

Re: Advanced Set Analysis instead of aggr

alex00321 — Thu, 18 Mar 2021 09:15:52 GMT

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!

Re: Advanced Set Analysis instead of aggr

19iv1987 — Thu, 18 Mar 2021 10:42:56 GMT

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.

Re: Advanced Set Analysis instead of aggr

agigliotti — Thu, 18 Mar 2021 11:44:34 GMT

Hi @19iv1987 ,

Maybe this:

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

I hope it can helps.

Best Regards

Re: Advanced Set Analysis instead of aggr

19iv1987 — Thu, 18 Mar 2021 11:50:54 GMT

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

Re: Advanced Set Analysis instead of aggr

agigliotti — Thu, 18 Mar 2021 16:11:51 GMT

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;

Re: Advanced Set Analysis instead of aggr

19iv1987 — Thu, 18 Mar 2021 17:32:08 GMT

Here my real data model

Re: Advanced Set Analysis instead of aggr

eddie_wagt — Fri, 19 Mar 2021 13:55:34 GMT

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 )