Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
carahyba
Partner - Contributor III
Partner - Contributor III

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
lironbaram
Partner - Master III
Partner - Master III

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
lironbaram
Partner - Master III
Partner - Master III

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 

carahyba
Partner - Contributor III
Partner - Contributor III
Author

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!
lironbaram
Partner - Master III
Partner - Master III

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 

carahyba
Partner - Contributor III
Partner - Contributor III
Author

Thanks, @lironbaram!