topic Set Analysis in AGGR Function in QlikView

Set Analysis in AGGR Function

BrunPierre — Fri, 10 Jul 2020 04:32:42 GMT

Hello Guys,

Just run out of ideas why the expression below still reacts to selections made in the [TRANSACTION TYPE] dimension;

SUM(AGGR(COUNT({$<Year={'$(vMaxYear)'},[TRANSACTION TYPE]=>}DISTINCT TransactionNumberToCount),[Transaction Number]))

Thanks.

Re: Set Analysis in AGGR Function

tresB — Fri, 10 Jul 2020 05:28:40 GMT

The additional scopes where you have to/can put set analysis are in bold below:

SUM( {} AGGR( {} COUNT({$<Year={'$(vMaxYear)'},[TRANSACTION TYPE]=>}DISTINCT TransactionNumberToCount),[Transaction Number]))

and in variable definition, may be like:

vMaxYear=Max( {} Year)

Re: Set Analysis in AGGR Function

BrunPierre — Fri, 10 Jul 2020 08:32:33 GMT

Thanks for that response, but then how would you combine them because your proposed expression seems incorrect.

Re: Set Analysis in AGGR Function

tresB — Fri, 10 Jul 2020 09:33:04 GMT

How exactly you made the changes to your expression? Let me know what makes you think that it is incorrect. Try to share a sample app.

Re: Set Analysis in AGGR Function

Kushal_Chawda — Fri, 10 Jul 2020 09:54:32 GMT

Just making it clear to what @tresB is trying to say

you may need to exclude the selection from your outer aggregation as well.

If you also don't want your vMaxYear to change for this selection, you may need to exclude the selection there as well

Assuming your variable expression is max(Year) then change it to max({<[TRANSACTION TYPE]=>}Year)

SUM({<[TRANSACTION TYPE]=>}AGGR(COUNT({$<Year={'$(vMaxYear)'},[TRANSACTION TYPE]=>}DISTINCT TransactionNumberToCount),[Transaction Number]))

Re: Set Analysis in AGGR Function

tresB — Fri, 10 Jul 2020 09:58:56 GMT

@Kushal_Chawda , also possibly in aggr().

Re: Set Analysis in AGGR Function

Kushal_Chawda — Fri, 10 Jul 2020 10:04:56 GMT

@tresB honestly I never used excluding field set in aggr, but it works fine even though we did not write set there

Re: Set Analysis in AGGR Function

tresB — Fri, 10 Jul 2020 10:33:56 GMT

@Kushal_Chawda , you are not wrong. However, there are cases when set analysis in aggr() is required, and since we don't know much deep about the requirement and business logic here it is good to go with the thumb rule (especially when one is not sure), i.e. - put set analysis in all possible aggregation functions and aggr() in the expression.