Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
cmano
Creator
Creator

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

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

cmano_1-1673333919830.png

 

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

  • For upper whisker the result is :  19.62375

cmano_2-1673334061793.png

 

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

 

Note that the table is not sorted.

 

thank you for your time

Labels (1)
1 Reply
jcmachado
Contributor III
Contributor III

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.