Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
nora7
Contributor II
Contributor II

Greater than average of top 5 values

I have a straight table in the form:

NameValue
a3
b6
c2
d7
e4
f1
g2
h8
i4
j3
k5
l1
m5
3

 

I need some help with being able to highlight those values which are greater than average of top 5 values.

In this case, top five values are: 8,7,6,5,5. and average of these values would be 6.2. So all the values in the table >6.2 need to be highlighted in a colour say blue.

I have tried using:

if(Values > if(rank(Values)<=5, sum(Values))/5, LightBlue(),LightGray())
in the 'Background Colour' option of the Values expression but it doesn't seem to work. Can this be done in the front end using the table options itself?

Can someone please help me with this?

Labels (1)
1 Solution

Accepted Solutions
MayilVahanan

HI @nora7 

Try like below

Color expression: If(Sum(Value) >= vRankAvgValue, LightBlue(),LightGray())

vRankAvgValue

 =Avg(Aggr(If(Rank(Sum(Value),4) <= 5, Sum(Value)), Name))

MayilVahanan_0-1617098364280.png

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

3 Replies
eddie_wagt
Partner
Partner

Maybe this is a solution. 

In background expression:

=if(
isnull(
avg({$<Name={"=num(subfield(rank(Value,1,3),'-',-1))<=5"}>}Value)
)
,LightGray()
,LightBlue()
)

 

MayilVahanan

HI @nora7 

Try like below

Color expression: If(Sum(Value) >= vRankAvgValue, LightBlue(),LightGray())

vRankAvgValue

 =Avg(Aggr(If(Rank(Sum(Value),4) <= 5, Sum(Value)), Name))

MayilVahanan_0-1617098364280.png

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

nora7
Contributor II
Contributor II
Author

Thank you so much @eddie_wagt and @MayilVahanan , this really helps!