Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Eddy
Contributor III
Contributor III

Set Analysis in Aggr function to deactivate filtering

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
];

 

Labels (3)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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

View solution in original post

3 Replies
tresesco
MVP
MVP

@Eddy 

Try like:

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

Eddy
Contributor III
Contributor III
Author

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?

tresesco
MVP
MVP

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