Loading

Find Closest Matching Number in Data Set

Udgivelsesdato: Apr 13, 2024
Opgave
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.
Trin
The example workbook, which can be downloaded from the right-hand pane of this article, 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 ABS([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 absolute 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.
Note: If two ID values are the same distance from the user input, then both values will show.

Variation: If there are multiple closest matches, show the lower match
  1. Modify [New ID] from the above instructions to the following:

    { FIXED : MIN(
    IF [ID] = [Select an ID]
    THEN [ID]
    ELSEIF ABS([Select an ID] - [ID]) = [Smallest Difference]
    THEN [ID]
    END
    )}

Adding the FIXED expression will return only one closest match for the entire data set. This will always return the lowest closest match because of the aggregation MIN(). To return the highest closest match, use MAX() instead of MIN()
Vidensartikelnummer

001458092

Vedhæftede filer

Show closest match_v2018.1.twbx

12 KB

 
Indlæser
Salesforce Help | Article