Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
did
Employee
Employee

Filter out 10% of dimension values around min/max KPI-values

Dear Community-Members,

I have the following table (in red are min & max values in respective year):

did_1-1729593353707.png

What whould be the KPI-Formula to show the same without 10% of regions having values equal or close to min/max values in each year (i.e. to show those 80% in between)?

Data set and app are attached here.

Any help appreciated.

Thank you

Labels (1)
5 Replies
Kushal_Chawda

@did  what is the expected output based on dummy data?

did
Employee
Employee
Author

@Kushal_Chawda 

Initially the range boundaries (values between min and max) in each year are as follows:

did_0-1729607834877.png

 

 

According to the posed requirement (show no those 10% of regions having values equal or close to min/max values in each year,  i.e. to show those 80% in between), if we take 3 (10% out of 30) min values (light blue cells,) and 3 (10% out of 30) max values (dark blue cells) we will have another table with different  range boundaries:

did_1-1729607834882.png

 

 

This is exactly what is needed.

Further it should be possible also to build the range chart without regions but having exactly these new range boundaries, something like this:

did_2-1729607834882.png

 

Kushal_Chawda

@did  Got it. Let me give it a try

Kushal_Chawda

@did  Idea is have below expression for excluding 10% of min and max values 

if(sum(KPI)<> min(total <Year> KPI) and
sum(KPI)<> min(total <Year> KPI,2) and
sum(KPI)<> min(total <Year> KPI,3) and
sum(KPI)<> max(total <Year> KPI) and
sum(KPI)<> max(total <Year> KPI,2) and
sum(KPI)<> max(total <Year> KPI,3), sum(KPI))

But, it should be dynamic based on Number of regions. You can follow below approach

Create two variables on frond end which will produce 10% count of region and above expression dynamically. You can create this expression dynamically in script as well

vCount10%_Region = 
Count(distinct Region)*0.1

vKPIFormula=
'If(' & 

concat(
'sum(KPI)<>min(total <Year> KPI,'& ValueLoop(1,$(vCount10%_Region)) &') and ',chr(10))

& chr(10) &

concat(
'sum(KPI)<>max(total <Year> KPI,'& ValueLoop(1,$(vCount10%_Region)) &')' & 
if(ValueLoop(1,$(vCount10%_Region))='$(vCount10%_Region)' ,', sum(KPI)',' and'),chr(10))

&')'


 

Create a chart with Dimension Region and Year. and measure $(vKPIFormula)

 

 

did
Employee
Employee
Author

@Kushal_Chawda very interesting approach (in script). Thank you very much!