Qlik Community

Qlik Sense App Development

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

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
Partner
Partner

Re: Set Analysis limitation, bug or misunderstanding?

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 

4 Replies
Partner
Partner

Re: Set Analysis limitation, bug or misunderstanding?

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 

Highlighted
Partner
Partner

Re: Set Analysis limitation, bug or misunderstanding?

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!
Partner
Partner

Re: Set Analysis limitation, bug or misunderstanding?

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 

Partner
Partner

Re: Set Analysis limitation, bug or misunderstanding?

Thanks, @lironbaram!