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: 
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
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Author

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
Author

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
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I believe my expression would exclude all 20%.

-Rob