13 Replies Latest reply on Dec 9, 2016 5:20 AM by Donald Wilson

    Find the MAX value of a Calculated Field

    Donald Wilson

      Hi group!


      I have created a report, part of which gives me the number of hours an employee has worked during the month.  This was done through a calculated field called "AVG Hours Worked" and it has the formula "AVG([Hours Worked])" within it.  I also have another calculated field called "TpSH" (Tickets per Staffed Hour) which uses the AVG Hours Worked field within it.  It reads, "([Tickets Completed]/[AVG Hours Worked])".  The "Tickets Completed" is another calculated field which uses the formula "COUNTD([Id Number]".  All of this information is displayed by month (January through December).


      I now need to find the MAX number of hours worked by any worker in a month for each month.  See my brief example:




      Although I have many more rows in the table, you can see that 'Don' worked 152 hours which will be the MAX hours for January.  In February 'Adam' worked 164 hours with is to become the MAX hours.  How is it that I calculate this for ALL employees each month?


      In advance, thanks for your help.



        • 1. Re: Find the MAX value of a Calculated Field
          Joe Oppelt

          WINDOW_MAX( [your aggregate calc here] )


          And you can tell a table calc to do the calc by [Employee] or by [Month] or across the whole table, or for all employees within each department, etc.

          • 2. Re: Find the MAX value of a Calculated Field
            Jeremy Harris

            If you want to show the full table with the max across the top, you can Totals using the settings in the screenshot below.

            If you just want the summary numbers, you can use an LOD like: MAX({ INCLUDE [Employee] : SUM([Hours Worked])}) to just get something like this screenshot.

            • 3. Re: Find the MAX value of a Calculated Field
              Donald Wilson

              Joe,  It's very possible I'm attempting your suggestion incorrectly.  I'll share what I've tried.


              I started making a new calculated field called Max Hours Worked.  In side it I wrote:

              WINDOW_MAX([Hours Worked])


              I'm getting the error message at the bottom of the page that says:

              "All fields must be aggregate or constant when using table calculation functions.

              or fields from multiple data sources."  What am I doing wrong?

              • 4. Re: Find the MAX value of a Calculated Field
                Donald Wilson

                Jeremy,  I clearly have something wrong as the numbers displayed are not real and they don't display correctly as I need.



                You can see in the above that the max hours work aren't consistent and the number is much to high.  The data table shows that Adam worked 152 hours in January and 168 in February.  Adrian worked 144 hours and January and 135 in February.


                Here is how I wrote my calculated field (called Max Hours Worked):

                MAX({INCLUDE [Coordinator (Ops Ytd Hours)]:SUM([Hours Worked])})


                I did try "AVG" instead of "SUM".  That made the numbers look a little better, but they aren't accurate.  It's still showing the maximum hours worked across the rows for the employee:

                Clearly I'm doing something incorrectly.  Any thoughts or ideas?


                Thanks for your interest and help.



                • 5. Re: Find the MAX value of a Calculated Field
                  Andrew Watson

                  As Joe mentioned you put your aggregate field into the table calculation. You have put a non-aggregated field, hence the error you're receiving.


                  Try this instead:


                  WINDOW_MAX(AVG([Hours Worked]))


                  I expect you could also do this using LODs but it's hard to take a stab at that with the information provided. It looks like you want to FIX for each month and employee the SUM number of hours worked, then use that result and FIX per month the MAX of that previous FIXED calculation.

                  • 6. Re: Find the MAX value of a Calculated Field
                    Joe Oppelt

                    I can help if I have a packaged workbook.

                    Specify your Tableau version when you upload it.

                    • 7. Re: Find the MAX value of a Calculated Field
                      Donald Wilson

                      Joe, I'm using Tableau version 9.0.


                      To clarify, to produce the report I'm combining two tables to create a productivity report.  These two tables are called:  OPS_YTD_TASKS_INCIDENTS and OPS_YTD_HOURS.  These are listed in order and are Left-Joined with the common field of "Coordinator".  I've attached the workbook I've started.  I'm struggling to get the work hours displayed correctly along with getting the value of the maximum hours to calculate per month.  I suppose it's possible it can't be done.

                      • 8. Re: Find the MAX value of a Calculated Field
                        Joe Oppelt

                        OK, I have this open.


                        Help me understand what you are really looking for.


                        For Adam Warren, in January, are those three numbers correct?  If not, what am I aiming for.  If so, what am I aiming for next?

                        • 9. Re: Find the MAX value of a Calculated Field
                          Donald Wilson



                          I'm attaching the hours that each worked by month.  I've loaded that into the table.  As an example though, Adam worked 152 hours in January, 168 in February and so forth.  I first need to get the sheet to show the correct hours worked each month.


                          Once that's done, the next task is to show the maximum hours worked by any employee by month.  Once I figure out how to do this, I can take it from there as I'm going to use this max hours to create some indexing.


                          Thank you again for your interest and help.  I'm new to this.  So it's greatly appreciated.



                          • 10. Re: Find the MAX value of a Calculated Field
                            Joe Oppelt

                            Your data set in Tableau doesn't show what your excel sheet shows.


                            When I pare the data down to Adam and January, I get 2320 rows.  And the sum of hours worked among those rows comes to 393,472.


                            So I looked at the data more,.  You have a [Work Year] and a [Work Month] dimension.  Even under a given year/month you have multiple records.  But I see that for a given year and month, the hours worked is the same for all rows.


                            So see sheet 2.


                            I made calcs to change those year/month fields to numeric values.  Much easier to deal with, in my opinion.  I inserted my year and month calcs.  And then, to get the singular HOURS value for a given cell, I grabbed MIN(hours).    Now we get the numbers you are aiming for.  (I think.)


                            Check this out before we move on.  Is this what you are really looking to see?

                            • 11. Re: Find the MAX value of a Calculated Field
                              Donald Wilson



                              Your numbers are correct.  I suppose you had 10 instances of each month because there were 10 months worth of data in the Hours Worked file.  I've kept working and discovered how it was calculating the hours worked so I created some calculated fields to account for that.  So look at what I've attached, especially the "Actual Hours Worked" and "Hours X Ticket Count".  When I figured out it had to be summed, that fixed it.


                              Now we have a successful first step.  The next step is to now find the Max number of hours by column (January, February, and so on).  For January that number is 152.  February is 168.  From there I can create my indexes that I'm after.

                              • 12. Re: Find the MAX value of a Calculated Field
                                Joe Oppelt

                                See attached.


                                Once you have a table created, you use table calcs to march through them.  WINDOW_MAX is a table calc function.  It says to take all the values in the table (using the "direction" I tell it to traverse the table) and find that value.  WINDOW_MAX, _MIN, _SUM, etc.  All different table calcs.


                                When you put a table calc on the sheet, you see a little triangle at the end of the pill.  Click on the triangle and you can select "Edit Table Calc"..  By default Tableau usually selects TABLE(across) as the "Compute using" setting.  But I changed it to TABLE(down).  That tells Tableau to do the operation on a column-by-column basis.  (TABLE(across) tells tableau to do it on a row-by-row basis.)  You can also tell it to run along various dimensions that are on the sheet.  And you can also choose "advanced" in that pulldown.  When you do, you can tell it to run through specific dimensions (in specific orders), and even to "restart every" occurrence of a given dimension.  So, for instance, you may have departments in which coordinators are grouped.  You could have it find the largest value for all the months among all the coordinators in each department.  Or you could have months grouped into quarters, and you could tell it to run down through the months, but restart every quarter.


                                So here I have told tableau to grab the largest value in each month.  TABLE(down) is all I need for that.  I didn't need any advanced settings for this.

                                • 13. Re: Find the MAX value of a Calculated Field
                                  Donald Wilson



                                  This has been a fantastic lesson for me.  Thank you greatly!  This is now working exactly as I needed it too.


                                  You are my hero!!