topic AGGR in Set Analysis Not Equivalent to IF Statement in App Development

AGGR in Set Analysis Not Equivalent to IF Statement

newuser — Tue, 17 May 2022 16:11:35 GMT

Hi - the following two expressions yield different results. I thought syntactically they are the same, but is there something different about how Qlik processes each of the two expressions below that could be causing different results?

I don't think the data model is the culprit, as the first expression using IF generates correct results. The only reason I can think of is, AGGR within set analysis doesn't process the same way as AGGR in an IF statement?

Option #1 - AGGR in IF statement - correct result:

sum({1} if(TranYr = CurrYr and placedt_all=aggr({1} nodistinct max({1}placedt_all),acctpol), -Payment))

Option #2 - AGGR in set analysis - incorrect result (higher value than expected):

sum( {1 <TranYr = {$(=CurrYr)},
placedt_all={"=max( aggr( {1} nodistinct max( {1} placedt_all),acctpol))"}
>} -Payment)

Re: AGGR in Set Analysis Not Equivalent to IF Statement

vinieme12 — Tue, 17 May 2022 17:01:28 GMT

In Option 1 ; max(aggr( will return a different max placedt_all for each acctpol

In Option 2 ; Set is evaluated once for the entire chart not for each acctpol

Re: AGGR in Set Analysis Not Equivalent to IF Statement

newuser — Tue, 17 May 2022 21:56:41 GMT

Thank you - can you explain what you mean by "once for the entire chart"? Does that mean it doesn't select the max placedt_all for each acctpol but rather an overall max placedt_all?

Re: AGGR in Set Analysis Not Equivalent to IF Statement

vinieme12 — Wed, 18 May 2022 03:47:34 GMT

"Does that mean it doesn't select the max placedt_all for each acctpol but rather an overall max placedt_all? "

Yes, this is correct

What's happening in your expression is since you are using aggr() and it can't return an array of values in SET Analysis this will just return NULL i.e placedt_all ={-} therefore this filter is never applied which is why you have higher values

can you explain what you mean by "once for the entire chart"?

Set Analysis is not evaluated for each row/each dimension value, for example Max(SomeValue) in SET ANALYSIS will return the max value for the entire dataset not MAx for each dimension value