topic Re: Aggregation inside SET Expression for Comparision in QlikView

Aggregation inside SET Expression for Comparision

baliyan_vinay — Sat, 16 Nov 2024 01:02:31 GMT

Hi Community,

I am having problem with the below expression

=Count(Distinct {<A_STAT = {"> 6050 <=6950"}, A_STAT_IND-={'C'}, A_STAT_FLG={'N'}, A_DET_STATUS=, Aggr(Max(A_CHK_NO),Link_Key)={" < 6964 "} >} Link_Key)

The Aggr part is not working for me, invalid modifier it says. What I am trying to do here is to see if maximum of A_CHK_NO is less than 6964 for my condition.

Thank you for your help.

Re: Aggregation inside SET Expression for Comparision

tresB — Tue, 17 Mar 2020 07:23:19 GMT

Modifier in set analysis is expected to be field and not a condition nor a value. You could try like:

=Count(Distinct {<A_STAT = {"> 6050 <=6950"}, A_STAT_IND-={'C'}, A_STAT_FLG={'N'}, A_DET_STATUS=, Link_Key ={"=Max(A_CHK_NO) < 6964 "} >} Link_Key)

Re: Aggregation inside SET Expression for Comparision

baliyan_vinay — Tue, 17 Mar 2020 07:34:38 GMT

Thanks for the answer.

The error is gone but the does not satisfy the condition.

The data looks like this

Link_Key	A_CHK_NO
1	6500
1	6700
1	6750

So the Aggr(Max(A_CHK_NO)) was meant to provide 6750 and then comparison against 6964 should satisfy the condition.

Normal expression works okay for me, see below. But because I need to exclude a filter selection(A_DET_STATUS) for the expression, I am trying the SET expression

=Count(Distinct If(A_STAT>6050 and A_STAT<=6950 and Aggr(Max(A_CHK_NO),Link_Key)<6964 and A_STAT_IND <> 'C' and A_STAT_FLG='N',Link_Key))

Re: Aggregation inside SET Expression for Comparision

tresB — Tue, 17 Mar 2020 07:58:08 GMT

Where are you using this expression, in a chart or text box? Could you share your sample app explaining expected output in that context?

Re: Aggregation inside SET Expression for Comparision

Brett_Bleess — Wed, 08 Apr 2020 20:46:34 GMT

The following Design Blog post may be helpful on this one too:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Please be sure to close out the thread if you solved things, you can post what you did if you figured it out on your own, and then use the Accept as Solution button on that post to close the thread.

Regards,
Brett