topic Re: Set analysis not working with AGGR in QlikView

Set analysis not working with AGGR

Edge — Sat, 16 Nov 2024 01:22:26 GMT

Hey guys,

I honestly don't understand why QlikView was designed in such an ill way that sometimes it doesn't work properly at all.

The below expression is supposed to calculate avg enrolled for lectures whose assign is LEC. Yet it fails to work with aggr:

=avg( {<ASSIGN_TYPE_NEW={"LEC"}>} Aggr(MERGED_ENROLLED, CLASS_NBR_NEW) )

I tried the below as well but none works:

=avg(Aggr(only({<ASSIGN_TYPE_NEW={"LEC"}>}MERGED_ENROLLED), CLASS_NBR_NEW))

=avg(Aggr(max({<ASSIGN_TYPE_NEW={"LEC"}>} MERGED_ENROLLED), CLASS_NBR_NEW))

I don't know why. I would recommend against all Qlik products. They are just poorly designed and the company is focused on milking customers.

Could someone please help? Note, one class_nbr_new could have multiple assign types, but I want the expression to calculate for assign_type_new = 'LEC' only

Re: Set analysis not working with AGGR

Kushal_Chawda — Sun, 02 Feb 2020 12:08:33 GMT

Would you be able to share sample data with expected output? Meanwhile you can try below

=avg(Aggr(sum({<ASSIGN_TYPE_NEW={"LEC"}>} MERGED_ENROLLED), CLASS_NBR_NEW))

Re: Set analysis not working with AGGR

Edge — Sun, 02 Feb 2020 12:25:06 GMT

Hey Kush,

Many thanks again. The above is not working. My issue is when I select NON in the filter, even if I have the Set Analysis, the expression is still calculating the value for NON assign types. The expression should show 0 instead of showing a value or the expression should be frozen on the average for LEC. Could you please take a look at the screenshot part of my original post?

Re: Set analysis not working with AGGR

Kushal_Chawda — Sun, 02 Feb 2020 12:25:04 GMT

for that you need to apply set analysis in outer aggregation as well

=avg({<ASSIGN_TYPE_NEW={"LEC"}>} Aggr(sum({<ASSIGN_TYPE_NEW={"LEC"}>} MERGED_ENROLLED), CLASS_NBR_NEW))

Re: Set analysis not working with AGGR

Kushal_Chawda — Sun, 02 Feb 2020 13:19:47 GMT

or try below

=alt(avg(Aggr(sum({<ASSIGN_TYPE_NEW*={"LEC"}>} MERGED_ENROLLED), CLASS_NBR_NEW)),0)

Re: Set analysis not working with AGGR

jduenyas — Thu, 06 Feb 2020 20:02:43 GMT

"...I don't know why. I would recommend against all Qlik products. They are just poorly designed and the company is focused on milking customers...."

Check it out here