Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
Rob, if the top 20% of sales were all the same value, would this exclude the whole top 20% or only the top 10%?
One clarification. For the top 5%, if there are 100 entries, it is removing the largest 5 entries.
I believe my expression would exclude all 20%.
-Rob