Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
LhZagora
Contributor
Contributor

Closeset Number

Hello experts

I have a problem to find the closeset number with qliksense .

Explination : 

I have a value x = 3 and i have a colunm A that contains (1,2,4,5,9).

I want a function that returns 2 as the closeset value to my x

if x for example change to 6 my closeset number will be 5

if x =2 then 2..... 

 

Thank you in advance.

 

1 Solution

Accepted Solutions
tresesco
MVP
MVP

When x=3, then you want 2 and not 4 (both being closest). Is it always such that in case of tie - you want lower value?

 

Assuming your x a variable, you could try like:

 

=FirstSortedValue(DISTINCT [Col A], fAbs([Col A]-VariableX))

 

 

View solution in original post

4 Replies
tresesco
MVP
MVP

When x=3, then you want 2 and not 4 (both being closest). Is it always such that in case of tie - you want lower value?

 

Assuming your x a variable, you could try like:

 

=FirstSortedValue(DISTINCT [Col A], fAbs([Col A]-VariableX))

 

 

LhZagora
Contributor
Contributor
Author

i want to be always the lower value. its not the case with this formula

tresesco
MVP
MVP

Try:

=FirstSortedValue( [Col A], if([Col A]<=VariableX,VariableX-[Col A]))



LhZagora
Contributor
Contributor
Author

doesnt work 

Capture.PNGCapture2.PNG