Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Community-Members,
I have the following table (in red are min & max values in respective year):
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
@did what is the expected output based on dummy data?
Initially the range boundaries (values between min and max) in each year are as follows:
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:
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 Got it. Let me give it a try
@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)
@Kushal_Chawda very interesting approach (in script). Thank you very much!