KNOWLEDGE BASE

Find Closest Matching Number in Data Set


Published: 23 May 2017
Last Modified Date: 23 May 2017

Question

How to find the closest matching number in the data set to a selection.

For example, if you should be able to select any number from a range, but there are missing ID numbers in the data, then show the closest matching ID number.

Environment

Tableau Desktop

Answer

The attached example workbook uses sample data set to demonstrate the following instructions:
  1. Click the down arrow next to Dimensions in the data pane and select Create Parameter
  2. In the Create Parameter dialog box, do the following and click OK:
    •  In the Name text box, type a name. In this example the parameter is named Select an ID.
    •  Under Properties, in the Data type list, select Integer.
    •  Under Allowable values, select Range.
    •  Set the Minimum and Maximum.
  3. Right-click [Select an ID] in the data pane and select Show Parameter Control.
  4. Create a calculated field with a name like Smallest Difference with a calculation similar to the following:
    { FIXED : MIN(ABS([Select an ID] - [ID]))}

    The above calculation uses the level of detail (LOD) function FIXED to return the smallest absolute difference between the selected [Select an ID] value and the data set value [ID] in the whole data set.

  5. Create a calculated field with a name like "New ID" with a calculation similar to the following:\
    IF [ID] = [Select an ID]
    THEN [ID]
    ELSEIF [Select an ID] - [ID] = [Smallest Difference]
    THEN [ID]
    END

    The above IF statement will return the data set value of [ID] if there is a value that matches the user selection. If there is not a matching value, then the IF statement will return the value of  [ID] whose difference matches the smallest absolute difference over the whole data set.

  6. Drag [New ID] onto the Rows shelf.
  7. Right-click Null and select Exclude.
Did this article resolve the issue?