3 Replies Latest reply on Mar 11, 2016 8:28 AM by Franck Nguyen

    Calculation : Closest Values

    Franck Nguyen

      Hi all,

       

      Maybe some of you faced this kind of troubles but I couldn't find a topic that solved my issue.

       

      I have a set of data which kinda looks like this :

       

       

      What I'm trying to do unsucessfully is the following :

      I want to create a table which uses Category and Sub-category as filters. No problem so far.

      Then I created a Parameter using ID's values in order to do that : If the user select an ID, the table displays the ID selected and the 5 closest IDs (based on the field 'Distance'). And that's where my troubles are. I can't find how to do that.

       

      Is there anyone who can help me ?

       

      Thanks in advance,
      Franck

        • 1. Re: Calculation : Closest Values
          Steve Mayer

          Franck - attached is a solution that uses an LOD calculation to solve your problem (Tableau 9.0 or higher required).

           

          Step 1

          Create a Parameter with your ID (use the Add from Field button to load all IDs in your data source). I called it [ID Parameter].

           

          Step 2

          Create a calculated field [Selected Distance] that returns the [Distance] of the selected ID Parameter. Since this is an LOD calculation, all rows in your data will have the same [Selected Distance] depending on which ID is selected in the Parameter. Formula is:

           

          { MIN(IF [ID] = [ID Parameter] THEN [Distance] END) }

           

          Step 3

          Create a calculated field that calculates the delta between Distance and Selected Distance, I called it [Delta]. I'm using absolute value, so that I get the closest distance - I don't care if it is higher or lower.

           

          ABS([Distance] - [Selected Distance])

           

          Step 4

          Create a calculated field that ranks IDs by their [Delta]

           

          RANK(SUM([Delta]), 'asc')

           

          Step 5

          Put it altogether on a Viz. Comes out like this:

          • 2. Re: Calculation : Closest Values
            Franck Nguyen

            Hi Steve,

             

            Thank you for the tip, it works very well for that kind of data.

            I'm trying to replicate it for an other project with pivoted data and a distance calculation field data but using several LoD does not seem to work. Basically everything looks to work fine except the step 2.

             

            Here are the steps I followed.

             

            Step 1 : Distance Calculation

            sum([Field1]) - ATTR({fixed : sum([Field1])}) +

            sum([Field2]) - ATTR({fixed :sum([Field2])})

             

            Step 2 :

            Set the ID Parameter

             

            Step 3 (former step 2) :

            if ATTR([Id]) = [Id Parameter] THEN [Dist] END

             

            Then I followed the former step 3 to step 5. The only difference between this dataset and the former dataset is that I have duplicated Ids related to the pivot.

             

            The result :

            Selected Distance should be displayed for every rows and it would work as expected but it seems that I can't use {fixed:} LoD.

            Capture.PNG

             

            Any clue ?
            Thanks again for your help and patience.

             

            Franck

            • 3. Re: Calculation : Closest Values
              Franck Nguyen

              Hi,

               

              I was able to solve the problem. I misused ATTR, which should be replaced by a sum (it doesn't change anything here). Then, I just have to replicate the calculation adding the IF [Id] = [Id Parameter] END and add a {fixed} LoD. Based on these two field, the delta can be calculated correctly.

               

              Thanks for your  help.

               

              Franck