
1. Re: Find the MAX value of a Calculated Field
Joe Oppelt Dec 6, 2016 1:16 PM (in response to Donald Wilson)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 Dec 6, 2016 2:16 PM (in response to Donald Wilson) 
3. Re: Find the MAX value of a Calculated Field
Donald Wilson Dec 7, 2016 9:25 AM (in response to Joe Oppelt)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 Dec 7, 2016 9:25 AM (in response to Jeremy Harris)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.
Don

5. Re: Find the MAX value of a Calculated Field
Andrew Watson Dec 7, 2016 10:23 AM (in response to Donald Wilson)As Joe mentioned you put your aggregate field into the table calculation. You have put a nonaggregated 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 Dec 7, 2016 2:51 PM (in response to Donald Wilson)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 Dec 8, 2016 6:03 AM (in response to Joe Oppelt)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 LeftJoined 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 Dec 8, 2016 8:06 AM (in response to Donald Wilson)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 Dec 8, 2016 9:42 AM (in response to Joe Oppelt)Joe,
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.
Don

Worked Hours.xlsx 23.0 KB


10. Re: Find the MAX value of a Calculated Field
Joe Oppelt Dec 8, 2016 10:12 AM (in response to Donald Wilson)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 Dec 8, 2016 11:45 AM (in response to Joe Oppelt)Joe,
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 Dec 8, 2016 12:02 PM (in response to Donald Wilson)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 columnbycolumn basis. (TABLE(across) tells tableau to do it on a rowbyrow 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 Dec 9, 2016 5:20 AM (in response to Joe Oppelt)Joe,
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!!