Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Remove top 5% of values from calcuation

I am re-creating an existing report in Qlik Sense. The report calculates the average number of days for the task to complete, but it removes the top 5% (longest number of days) from the calculation. Since the user can select any date, there is no way to pre-compute this during the load. Is this possible to do in Qlik Sense?

5 Replies

Re: Remove top 5% of values from calcuation

Here'a an example of calculating avg(Sales) for the Month excluding top and bottom 10%.

avg(if(Sales > aggr(NODISTINCT fractile(Sales, 0.1), Month) and Sales < aggr(NODISTINCT fractile(Sales, 0.9), Month),Sales))

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Not applicable

Re: Remove top 5% of values from calcuation

Hi Mike,

You could try modifying the dimension in a chart.  Using the Limitation setting you can set values for Top and Bottom and Relative values.

As an example you could set it to show the bottom 95% using a calculated expression.

Hope this helps.

Rod

Not applicable

Re: Remove top 5% of values from calcuation

Rob, if the top 20% of sales were all the same value, would this exclude the whole top 20% or only the top 10%?

Not applicable

Re: Remove top 5% of values from calcuation

One clarification. For the top 5%, if there are 100 entries, it is removing the largest 5 entries.

Re: Remove top 5% of values from calcuation

I believe my expression would exclude all 20%.

-Rob

Community Browser