7 Replies Latest reply on Aug 14, 2013 8:57 AM by Pedro Machado

    Get Name from max value of a table.

    Kris Erickson

      Okay,  I think I am close, but I cannot seem to figure out the rest of the solution.

       

      I have a chart with Averages and Max.  I want to have a tool tip (to the right) over the max value with the name of the person who is that max value:

       

      I built a test workbook that contains a calculated field with this formula to try and get the name of the max value:

      if first() == 0 then

      lookup(attr([Name]),first())

      end

       

      As you can see the it gives me the name of the first person, but not the first person for each college.  Does anyone know how I need to organize the NameMax table calculation?  I can't seem to figure out a way.

       

      Thanks and the full workbook can be found here:

      Workbook: GAUniversitySystem

        • 1. Re: Get Name from max value of a table.
          Jim Wahl

          It seems like you need to adjust the table calc's compute using. I find it easier to directly specify the compute using rather than use the relative Table (down), ...

           

          In your second example above, right-click on the table calc NameMax and select Edit Table Calculation. In the compute using box, select Advanced.

           

          You'll see two boxes for partitioning and addressing, and a sort option at the bottom. In your example, you'll want Name on the addressing side and Organization on the partitioning. This will return find the first name for each Organization. You may also want to specify the sort order for Name, which I assume Salary descending.

           

          Jim

          • 2. Re: Get Name from max value of a table.
            Pedro Machado

            Hi Kris,

             

            I tried NameMax2 = LOOKUP(max(Name),first()) Addressing [Organization (group), Name] and Restarting every Organization (group). You have to edit the table calculation and choose "Compute using: Advanced...". See the right side of the screenshot.

             

            This seems to be pulling the right name.

             

            I changed your MaxPay view to show the top 3 employees (index <= 3) next to NameMax2 and you can see the top earner's name is shown in the NameMax2 column.

             

            Is this what you were looking to do?

             

            maxname.PNG.png

             

            Pedro

            1 of 1 people found this helpful
            • 3. Re: Get Name from max value of a table.
              Kris Erickson

              Pedro,

               

              Thanks for the response.

               

              On a side-note, when I opened my workbook back up, it briefly said "nulo" (Spanish) before it reverted back to English.  Did you see "null" before it converted to "nulo"?  I wonder if the Tableau public workbooks save the last viewed language.

               

              Interesting.

              • 4. Re: Re: Get Name from max value of a table.
                Jim Wahl

                Hi Kris,

                 

                I took another look at your workbook. I don't think the above solutions for your test MaxPay worksheet will help you accomplish your goal of displaying the name of the person with the max salary in the graphical distribution "Display" worksheet.

                 

                The problem is that the Display worksheet doesn't have the name level of detail and, therefore, the Tableau database query will not return the names. Instead the worksheet is at the Organization level of detail, which means that when Tableau queries the database, it'll be calculating the aggregates AVG(Salary), MAX(Salary), ..., for each Organization.

                 

                There are two options here: 1) Adding Name to the level of detail and using table calcs to find the aggregates (MAX, AVG, Median, ...) or 2) using custom SQL to generate a list of the max salary and name for each organization.

                 

                I'll describe both below. In general (2) should give you better performance, since (1) will require bringing all of the names into the view. Option 2 also allows you to only show the name when hovering over the max salary data point.

                 

                Table Calc Approach

                Once you add Name to the level of detail shelf, you're gong to get an aggregate for each name, see the right-image below.

                2013-08-14 10-54-36.png

                To get back to one measure per organization, you need to use the WINDOW_xxx functions. For example, AVG(Salary) becomes WINDOW_AVG(AVG(Salary)). Also since we only need to calculate this value once for each organization, these WINDOW aggregates should be wrapped in an IF FIRST() == 0 THEN <Function> END.

                 

                You'll need to convert all of your normal aggregates to window aggregates (really, aggregates of aggregates). After replacing the measures in the view and setting compute using the Name, you'll get back to your original view (slight difference, I've sorted the rows by avg salary descending to match the table sort order):

                2013-08-14 11-01-43.png

                 

                Now you can add your NameMax field. And then edit the formula to remove the IF FIRST() ==0, since you want this value for all marks on the view. It's then just LOOKUP(ATTR(Name), FIRST()). The addressing / compute using is just Name, but I'd still select the advanced compute using dialog and set the sort order to be MAX(Salary) descending.

                 

                The problem with this approach is that you're bringing back all 100K rows (for 2012) into the view, which makes it slow on a laptop (5 seconds on mine) and very slow if you're going to deploy this to Tableau Server. Plus you need to use a bunch of table calcs, which isn't bad here, but can get a little messy if you're doing secondary calculations. Lastly, I don't think it's possible to show the Name only when hovering over the Max Salary mark.

                 

                Custom SQL

                To avoid these issues, I opened a new connection to your Excel spreadsheet with custom SQL that generates a table with columns for Organization, Fiscal Year, Max Salary by Org, Max Salary Name by Org. This table only has 74 rows.

                 

                There are multiple way to write the SQL, but I think the clearest is to use a query to find the max salary and then join this with the main table to add the name.

                 

                Here's the query that finds the max salary for each organization and fiscal year:

                SELECT [Sheet1$].[Organization] AS [Organization],
                  [Sheet1$].[Fiscal Year] AS [Fiscal Year],
                  MAX([Sheet1$].[Salary]) AS [Salary]
                FROM [Sheet1$]
                GROUP BY [Organization], [Fiscal Year]
                
                
                

                 

                To add the name to this, you need to do an outer join on the main table, matching on organization and fiscal year:

                SELECT [Sheet1$].[Organization] AS [Organization],
                  [Sheet1$].[Fiscal Year] AS [Fiscal Year],
                  [Sheet1$].[Name] AS [Name (Max Salary)],
                  [Sheet1$].[Salary] AS [Max Salary]
                FROM [Sheet1$]
                INNER JOIN (
                        SELECT [Sheet1$].[Organization] AS [Organization],
                          [Sheet1$].[Fiscal Year] AS [Fiscal Year],
                          MAX([Sheet1$].[Salary]) AS [Salary]
                        FROM [Sheet1$]
                        GROUP BY [Organization], [Fiscal Year]) t2
                    ON [Sheet1$].[Organization] = t2.[Organization] AND
                       [Sheet1$].[Fiscal Year] = t2.[Fiscal Year] AND
                       [Sheet1$].[Salary] = t2.[Salary]
                
                
                

                 

                The second code segment here is what you'd plug into Tableau: Edit Connection > Custom SQL.

                 

                Blending in Data

                My first thought was to just add the max name using blending. You'd use your original "Display" worksheet, but add Name (Max Salary) from the secondary data source. And make sure you're linked to the primary source on Organization and Fiscal Year. It'll look something like this:

                2013-08-14 16-17-46.png

                 

                The problem is that you still can't (as far as I can tell, anyway) prevent the name from appearing on the tooltip for all marks.

                 

                Use Dual Axis

                The solution I came up with is to use two marks and dual-axes to put the charts on the same graph. The first set of marks includes everything but the Max Salary, which is brought in from the secondary data source.

                 

                To create this view, drag Max Salary from the secondary data source to the columns shelf. This will create two graphs. You can edit the tooltip to show this value and compare it to the MAX(Salary) on the left graph. When you're happy that max salary is working, you can remove the pill from the left graph's measures shelf. (Again, make sure the secondary data source is linked on both organization and fiscal year.)

                 

                Then right-click on the Max Salary pill on the cols shelf and select Dual Axis and Synchronize Axis. Then of course you can unselect show header. After editing the colors, shapes and tooltip, you'll get something like this:

                2013-08-14 16-29-48.png

                 

                Attached is the workbook. Let me know if you have any questions---and don't worry if your reply is (Thanks. But I was happy with your first answers!)

                 

                Jim

                1 of 1 people found this helpful
                • 5. Re: Re: Get Name from max value of a table.
                  Kris Erickson

                  That's awesome!  I will have to apply that.  I just did a simple solution of making a 2nd worksheet and placing the names in there like this:  Dashboard 3

                   

                  Which is why I love Tableau, it's more like a loose collections of drawing tools than a standard way to do X.

                   

                  Typically when I show a large number of data points, with Average (or Median or Stdevs), I show all data points, use reference lines for the Average and Max and then have their name in the tooltip.  Typically when showing Max, people want to know who the max is so in the past I've had to put everyone.  Past Solution:

                  Sheet 7

                   

                  I wanted to reduce the amount of work Tableau is trying to do by removing the detail 'off the shelf' but still retain one name for the tooltip.  Your solution is going to provide a way.

                  • 6. Re: Re: Get Name from max value of a table.
                    Jim Wahl

                    I like the layout on your Dashboard 3 a lot. Clear and doesn't require any hovering, which is always nice, especially since Tableau's tooltips are bit finicky.

                    1 of 1 people found this helpful
                    • 7. Re: Re: Get Name from max value of a table.
                      Pedro Machado

                      Interesting. My computer's language is set up as English and I my views where showing "null". Not sure what's going on.

                       

                      Pedro