Need a help in correcting the set analysis Expression

[kunkumnaveen]

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 

[sunny_talwar]

Try this

Avg(Aggr(If(Rank(-Avg(Value), 4) <= 5, Value), Questions, ID))

[kunkumnaveen]

Hi ,Thanks for your quick reply ,
wat about if i need avg of Bottom 100 values

is this right.
Avg(Aggr(If(Rank(-Avg(Value), 4) <= 100, Value), Questions, ID))

[sunny_talwar]

I would think so...

[kunkumnaveen]

Some where some thing is missing for bottom 5 i am getting ,but for bottom 100 i am missing few values using above expression

[sunny_talwar]

Can you elaborate with an example or a sample?

[kunkumnaveen]

Hi all,
Is there any other way in writing below expression
aggr(avg({<ID={"=Rank(-avg({<FY={$(vFY)}>}Value),4)<=5"}>}Value),Questions)

Variable
vFY=max(FY)

where FY isa field with value,2018,2017,2016,2015


i tried below expression
Avg(Aggr(If(Rank(-Avg(Value), 4) <= 5, Value), Questions, ID))
but when i put FY in expression
Avg(Aggr(If(Rank(-Avg({<FY={$(vFY)}>}Value), 4) <= 5, Value), Questions, ID))
i am not getting proper output

[sunny_talwar]

What exactly are you trying to accomplish by re writing your set analysis expression? Is it not working the way you expect or you just want to use another way of doing the same thing?

[kunkumnaveen]

HI,
The thing is ,i have got 4 FY years of data ,so in each fiscal year there might be differ in ID's which falls under top 5 ,so i am trying to differentiate ID's w.r.t there Fiscal year .So that is reason why i am trying to put FY in set analysis .
Avg(Aggr(If(Rank(-Avg({<FY={$(vFY)}>}Value), 4) <= 5, Value), Questions, ID))
Variable
vFY=max(FY)
where FY is a field with values=2018,2017,2016,2015
But the issue is when i have not selected any Fiscal year by default it should pick max FY value which is (2018),but in my case it is not working properly unless i select a Fiscal Year

[kunkumnaveen]

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