Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Set analysis not working with AGGR

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

Capture.PNG

Labels (3)
2 Solutions

Accepted Solutions
Highlighted
MVP
MVP

Re: Set analysis not working with AGGR

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))

View solution in original post

Highlighted
MVP
MVP

Re: Set analysis not working with AGGR

or try below

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

View solution in original post

5 Replies
Highlighted
MVP
MVP

Re: Set analysis not working with AGGR

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))

Highlighted
Creator
Creator

Re: Set analysis not working with AGGR

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?

Highlighted
MVP
MVP

Re: Set analysis not working with AGGR

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))

View solution in original post

Highlighted
MVP
MVP

Re: Set analysis not working with AGGR

or try below

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

View solution in original post

Highlighted
Specialist
Specialist

Re: Set analysis not working with AGGR

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