Aggregation inside SET Expression for Comparision

[baliyan_vinay]

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.

[tresesco · Accepted solution]

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)

[baliyan_vinay]

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

[tresesco]

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

[Brett_Bleess]

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