Fetch equal-greater & equal-lower value from a table

[cmano]

Hello guys.

I have this expression at QlikView in boxplot (upper and lower whisker).

• Lower Whisker:

=FractileExc(If(Characteristic='γ',Value), 0.25) - 1.5 *(FractileExc(If(Characteristic='γ',Value), 0.75)-FractileExc(If(Characteristic='γ',Value), 0.25))

• Upper Whisker: 

FractileExc(If(Characteristic='γ',Value), 0.75) + 1.5*(FractileExc(If(Characteristic='γ',Value), 0.75)-FractileExc(If(Characteristic='γ',Value), 0.25))

For lower whisker the result is :  7.93375

I want the QlikView bring the first value >=  7.93375 ie =8.44

• For upper whisker the result is :  19.62375

 I want the QlikView bring the first value <=  19.62375  ie =17

Note that the table is not sorted.

thank you for your time

[jcmachado]

You can use the Aggr() function and the Min() function to find the first value that is greater than or equal to the lower whisker and less than or equal to the upper whisker. Here's an example of how you can do this:

1. In the "Lower Whisker" expression, you can use the following formula to find the first value that is greater than or equal to the lower whisker: =Min(Aggr(If(Characteristic='γ' and Value>=Lower Whisker,Value), Value))

2. In the "Upper Whisker" expression, you can use the following formula to find the first value that is less than or equal to the upper whisker: =Min(Aggr(If(Characteristic='γ' and Value<=Upper Whisker,Value), Value))

This will give you the first value that is greater than or equal to the lower whisker and the first value that is less than or equal to the upper whisker, respectively.

Please note that this is just an example, you might want to adjust the formula according to your data and needs.

It could be that you need a sort() function to get the right values.