Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I am trying to write a set analysis expression to get Bottom 5 values.
The data has three column, ID,Question,Value.
In a straight table in took
Dimension:Question
Expression:
aggr(avg({<ID={"=Rank(-avg(Value),4)<=5"}>}Value),Questions)
Output: By using above expression the out put is
Which is Wrong ,Let say for Question :03 the Bottom 5 avg value has to be =0.60(0+0+1+1+1/5)
but i am getting 1.00(1+1+1+1+1/5) as output same with rest .
what i find out is my expression is ignoring zero value rows and considering only 1 and above values rows .
if i select a value from question let say 03 then i am getting required output which is 0.60 ,without selection i am not getting
SO how to include zero value rows in my expression
PFA sample data
Thanks
because
Try this
Avg(Aggr(If(Rank(-Avg(Value), 4) <= 5, Value), Questions, ID))
Continue 2.....
Before selecting any value from FY
After Selecting 2018 from FY
Which is correct output should get same even if i havent select any value from FY field because i am passing Max value through variable
PFA the raw data for reference