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

Retrieve value from different column on same row

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

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

Thanks so much Simon!

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