Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a straight table in the form:
Name | Value |
a | 3 |
b | 6 |
c | 2 |
d | 7 |
e | 4 |
f | 1 |
g | 2 |
h | 8 |
i | 4 |
j | 3 |
k | 5 |
l | 1 |
m | 5 |
n | 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?
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))
Maybe this is a solution.
In background expression:
=if(
isnull(
avg({$<Name={"=num(subfield(rank(Value,1,3),'-',-1))<=5"}>}Value)
)
,LightGray()
,LightBlue()
)
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))
Thank you so much @eddie_wagt and @MayilVahanan , this really helps!