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

cmano — Tue, 10 Jan 2023 07:07:59 GMT

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

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

jcmachado — Wed, 25 Jan 2023 21:10:14 GMT

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:

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

topic Re: Fetch equal-greater & equal-lower value from a table in QlikView

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

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