Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

Need a help in correcting the set analysis Expression

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

Capture1.PNG

Which is Wrong ,Let say for Question :03 the Bottom 5 avg value has to be =0.60(0+0+1+1+1/5)

 

 

Capture.PNG

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

Capture1.PNG

 

SO how to include zero value rows in my expression

PFA sample data

 

Thanks

 

 

 

because 

Labels (1)
10 Replies
sunny_talwar

Try this

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

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

Continue 2.....

Before selecting any value from FYCapture.PNG

After Selecting 2018 from FY

Capture.PNG

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

Capture.PNG

 

PFA the raw data for reference