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: 
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
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

3 Replies
eddie_wagt
Partner - Creator III
Partner - Creator III

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
Please close the thread by marking correct answer & give likes if you like the post.
nora7
Contributor II
Contributor II
Author

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