Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Set Analysis limitation, bug or misunderstanding?

Hi there! 😃

I'm trying to calculate averages without outliers. I've used the FRACTILE function to determine which numbers are my outliers and exclude them from my AVG. Basically, the Function 1 (in this example) brings a value that is not the same when it is inside a set analysis condition, on the same tuple.

[Function 1] FRACTILE({<[QTD]-={0}>} [QTD],0.75)+(1.5*(FRACTILE({<[QTD]-={0}>} [QTD],0.75)-FRACTILE({<[QTD]-={0}>} [QTD],0.25)))
RESULT: 599,75

ROUND(AVG({<[QTD]-={">=$(=FRACTILE({<[QTD]-={0}>} [QTD],0.75)+(1.5*(FRACTILE({<[QTD]-={0}>} [QTD],0.75)-FRACTILE({<[QTD]-={0}>} [QTD],0.25))))<=-214,25"}>}[QTD]))
RESULT: 157 (should be 191)

ROUND(AVG({<[QTD]-={">=599,75<=-214,25"}>}[QTD]))
RESULT: 191

In the attached file, there is a column hardcoded for one dimension value to show this problem. Please, I appreciate any help.

OBS: Previous calculation at load script would be a solution for this scenario, but it's not factible at the real one because I have many others metrics and dimensions to do the same calc. Must be dynamic.

Thanks and regards!

Labels (4)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

HI

your expression should look like this 

avg(aggr(if(QTD<= $(LSup([QTD],[REGIONAL])) and QTD >=$(LInf([QTD],[REGIONAL])) ,QTD),ID,REGIONAL))

you can't use set analysis because it will return one number to all regions and you want different limits to each region 

View solution in original post

4 Replies
Highlighted
Partner
Partner

HI

your expression should look like this 

avg(aggr(if(QTD<= $(LSup([QTD],[REGIONAL])) and QTD >=$(LInf([QTD],[REGIONAL])) ,QTD),ID,REGIONAL))

you can't use set analysis because it will return one number to all regions and you want different limits to each region 

View solution in original post

Highlighted
Partner
Partner

Hi!

It's worked! Nice! But do you know why the function below doesn't?

ROUND(AVG({<[QTD]-={">=$(=$(LSup([QTD],[REGIONAL]))<=$(=$(LInf([QTD],[REGIONAL])))"}>}[QTD]))

Thanks!
Highlighted
Partner
Partner

because 

you can't use a nested aggregation inside aggregation 

the aggr convert the inner part to virtual table and than you can use an aggregation function 

Highlighted
Partner
Partner

Thanks, @lironbaram!