topic Need a help in correcting the set analysis Expression in App Development

Need a help in correcting the set analysis Expression

kunkumnaveen — Fri, 02 Apr 2021 07:42:04 GMT

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

Re: Need a help in correcting the set analysis Expression

sunny_talwar — Mon, 26 Nov 2018 12:56:27 GMT

Try this

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

Re: Need a help in correcting the set analysis Expression

kunkumnaveen — Mon, 26 Nov 2018 13:23:37 GMT

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))

Re: Need a help in correcting the set analysis Expression

sunny_talwar — Mon, 26 Nov 2018 13:25:13 GMT

I would think so...

Re: Need a help in correcting the set analysis Expression

kunkumnaveen — Mon, 26 Nov 2018 13:35:06 GMT

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

Re: Need a help in correcting the set analysis Expression

sunny_talwar — Mon, 26 Nov 2018 13:41:00 GMT

Can you elaborate with an example or a sample?

Re: Need a help in correcting the set analysis Expression

kunkumnaveen — Tue, 27 Nov 2018 05:40:48 GMT

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

Re: Need a help in correcting the set analysis Expression

sunny_talwar — Tue, 27 Nov 2018 12:29:47 GMT

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?

Re: Need a help in correcting the set analysis Expression

kunkumnaveen — Wed, 28 Nov 2018 05:05:16 GMT

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

Re: Need a help in correcting the set analysis Expression

kunkumnaveen — Wed, 28 Nov 2018 05:13:30 GMT

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

Re: Need a help in correcting the set analysis Expression

sunny_talwar — Wed, 28 Nov 2018 09:59:03 GMT

Try this

Avg(Aggr(If(Rank(-Avg({<FY={$(=$(vFY))}>}Value), 4) <= 5, Only({<FY={$(=$(vFY))}>}Value)), Question, ID))