2 Replies Latest reply on Jul 24, 2016 2:51 PM by Kris Hallis

    Retrieve value from different column on same row

    Kris Hallis

      Hi,

      I am putting together a Track and Field workbook.

      One of the sheets is designed to show the best result (School Best / SB) for each event for each school. I have got this working.

      However, I want to be able to hover over the result and for Tableau to display the name of the student who achieved that result.

      I've tried to create a calculated field called 'SB Student Name **' with the following code:

       

      if [SB (To Display)] = attr([Result (To Display)]) then

      max([Student Name])

      END

       

      However, obviously this does not work.

       

      I have attached a sample workbook and would appreciate any help.

      If you go to the 'School Comparison' worksheet and hover over a result, you will notice the 'Student Name' is blank,

      Thank you very much.

        • 1. Re: Retrieve value from different column on same row
          Simon Runc

          hi Kris,

           

          So one way to do this is to create the SB as an LoD, which means that although the result is calculated as an aggregate (the MAX or MIN, depending on Track or field, is calculated at each School/Event combination), it is returned at row level and so acts like a real dimension...

           

          So first I created a FIXED LoD for the SB

          [SB - LoD]

          IF [Event Type] = "Track" THEN {FIXED [School],[Event]: MIN([Result (To Display)])}

          ELSE {FIXED [School],[Event]: MAX([Result (To Display)])}

          END

           

          One thing to note...this currently doesn't always work as your results are evaluated as strings....meaning the MIN/MAX is based on Alphabetic order. Which in most cases does actually work, but not for all. I would convert all your results as values (meters for Fields, and Seconds for Track). You can still use the string in any labels, but can use the value version in the above.

           

          Once we have the SB, we can then put in the following (row level) formula to pick up the student name

          [SB - LoD Student Name]

          IIF([SB - LoD] = [Result (To Display)],[Student Name],NULL)

           

          The 'working - SR' tab should show you what's going on here...

           

          I can then bring this field into your ToolTip.

           

          Hope that makes sense, and does the job, but please post back if not

          • 2. Re: Retrieve value from different column on same row
            Kris Hallis

            Thanks so much Simon!

            I just changed MIN([Result (To Display)] to MIN([Result]) and it worked perfectly.