3 Replies Latest reply on Aug 8, 2016 9:09 AM by swaroop.gantela

Return nearest value

Jonathan Oakleigh Aug 7, 2016 10:36 PM

Hello,

I have a dataset that looks something like this:

Values in table 1	Values in table 2	Closest Value
153.43	153.2	153.44
543.55	153.44
33.45	154.6
53.26	154.7

What I need to do is have Tableau look at the value in Table 1, then scan the Table 2 column to identify the closest value, and then return that value.

To quote many who have gone before me "I feel like this is something Tableau should be able to do..." haha.

Any help would be greatly appreciated!

Thanks

1. Re: Return nearest value

Rohan Malusare Aug 7, 2016 11:42 PM (in response to Jonathan Oakleigh)
Hi Jonathan ,
Are you looking for this?

Please check attached sample workbook and let me know.

Thanks and regards,
Rohan Malusare.

Closest_value.twbx 30.5 KB
Like Show 0 Likes(0)

Actions
2. Re: Return nearest value

Mahfooj Khan Aug 8, 2016 12:51 AM (in response to Jonathan Oakleigh)
I've tried to get the closest value as per your requirement. Have a look and let me know If you've any query.
Workbook (version 9.3) has been attached for your reference.

Mahfooj

Closest_value.twbx 30.5 KB
Like Show 0 Likes(0)

Actions
3. Re: Return nearest value

swaroop.gantela Aug 8, 2016 9:09 AM (in response to Jonathan Oakleigh)
Jonathan,

I may not have caught the gist,
but I had the feeling that you are going to need to do a self-join to get every
combination of Table1 value with every Table2 value.

The difference equation is
WINDOW_MIN(MIN(ABS([Distance])))

where Distance is
[Table1]-[Table2 (Sheet1$1)]

And then the closest is
IF ATTR([Table2 (Sheet1$1)])=
    (IF ATTR(ABS([Distance]))=[MinDistanceTable2]
    THEN ATTR([Table2 (Sheet1$1)])
    END)
THEN ATTR([Table2 (Sheet1$1)])
END

212661close.twbx 43.8 KB
Like Show 0 Likes(0)

Actions