topic Re: Set Analysis in Aggr function to deactivate filtering in App Development

Set Analysis in Aggr function to deactivate filtering

Eddy — Sat, 16 Nov 2024 01:23:44 GMT

Example QVF attached

Hello Experts,

I have the following KPI, in which i want to deactivate filtering in field Status through set expression:

Sum(Aggr(count( {<Flag = {1}, Status =, Date = {">=20200901<=20200905"}> } ID),Region))

It does not work, this KPI still reacts to filtering in Status.

This would work, but then I would have to sum up every new region, so that is not what I want:

count( {<Flag = {1}, Status =, Date = {">=20200901<=20200905"}, Region = {'West'}> } ID) +
count( {<Flag = {1}, Status =, Date = {">=20200901<=20200905"}, Region = {'East'}> } ID) +
count( {<Flag = {1}, Status =, Date = {">=20200901<=20200905"}, Region = {'North'}> } ID) +
count( {<Flag = {1}, Status =, Date = {">=20200901<=20200905"}, Region = {'South'}> } ID)

Is it possible to use aggr function and deactivate filtering in set analysis?

My Data:

Load
*,
Date as %DateKey;
Load * Inline [
Date, Flag, Status, Region, ID
20200901, 1, A, West, 1
20200901, 1, A, West, 2
20200901, 1, A, West, 3
20200901, 1, A, West, 4
20200901, 1, C, West, 5
20200901, 1, C, West, 6

20200903, 1, A, East, 7
20200903, 1, B, East, 8
20200903, 0, A, East, 9
20200903, 0, C, East, 10

20200905, 1, A, North, 11
20200905, 0, A, North, 12

2020910, 1, A, South, 13
2020910, 1, B, South, 14
2020910, 0, A, South, 15
2020910, 1, A, South, 16
2020910, 1, C, South, 17
2020910, 1, C, South, 18
];

Re: Set Analysis in Aggr function to deactivate filtering

tresB — Thu, 24 Sep 2020 07:13:43 GMT

@Eddy

Try like:

Sum( {<Status>} Aggr(count( {<Flag = {1}, Status =, Date = {">=20200901<=20200905"}> } ID),Region))

Re: Set Analysis in Aggr function to deactivate filtering

Eddy — Thu, 24 Sep 2020 07:45:51 GMT

Thank you Sir, that works.

I just want to understand the logic: I use concat to show the values for the calculation

East 2

North 1

West 6

concat({<Status>} ( Aggr( count( {<Flag = {1}, Date = {">=20200901<=20200905"},Status=> } ID),Region)),' + ')

Result: 1 + 2 + 6

-> this does not react to filter in Status

concat( ( Aggr({<Status>} count( {<Flag = {1}, Date = {">=20200901<=20200905"},Status=> } ID),Region)),' + ')

-> this reacts to filtering in Status

Could you explain please why {<Status>} should be outside of the aggr function?

Re: Set Analysis in Aggr function to deactivate filtering

tresB — Thu, 24 Sep 2020 09:20:37 GMT

Because, cancat() is an aggregation function and if you don't put the set modifier in there, for aggr() the values are already reduced.