# Qlik Sense App Development

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)

• ### Set Analysis

1 Solution

Accepted Solutions
Highlighted
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
Highlighted
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

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

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

## Re: Set Analysis limitation, bug or misunderstanding?

Thanks, @lironbaram!