4 Replies Latest reply on Feb 22, 2016 11:43 AM by Darin Friedrichs

# Find year corresponding to max value

I'm stuck on a problem where I'm trying to color a map based on which year had the max sales

I can easily add sales to the visualization and aggregate using MAX and I get the correct max sales. However I then want to color it based on the year of the max sales

Here is some sample data

 State Year Sales California 2012 557 California 2013 421 California 2014 305 Oregon 2012 125 Oregon 2013 226 Oregon 2014 605

So I can map this and get the correct max sales values. But I would like to color the state based on which year had those max sales (for example red for 2012, blue for 2013, green for 2014, etc....)

Any ideas or suggestions on how to do this?

Thanks

• ###### 1. Re: Find year corresponding to max value

There may be specific things in your data shape or your sheet that would dictate how you do it, but one way that would work is this:

(I'm typing this off the top of my head, so you may need to tinker with syntax...)

LOOKUP(IF sum([Sales] = WINDOW_MAX(sum([Sales] then [Year] end)

Something like that.

That will be a table calc.  You'll want to make sure it addresses the rows on a state-by-state basis so that it doesn't return the year value for the largest Sales across the country.

• ###### 2. Re: Find year corresponding to max value

Or you can do this with LODs (available post Tableau version 9.0)

Year with Max Sales: If [Sales] = { fixed [State] : max([Sales]) } then [Year] end

Max Sale Value by State: { fixed [State] : max([Sales]) }

Place the 1st calc on color and 2nd calc on label. You will need to set the colors of your preference by clicking on the caret of the color legend and selecting 'edit colors'

1 of 1 people found this helpful
• ###### 3. Re: Find year corresponding to max value

Awesome this works greats, thanks!

• ###### 4. Re: Find year corresponding to max value

One other thing I wanted to add on here for anyone else's reference. I also wanted to run the 2nd highest sales amount and year to include in the workbook and below are the adapted formulas to get that

For 2nd highest sales amount

{include [State]: max(if [Sales] != [Max Sales] then [Sales] end)}

For the year which corresponds with the 2nd highest sales year

if [Sales] = {include [State]: max(if [Sales] != [Max Sales] then [Sales] end)} then [Year] END

1 of 1 people found this helpful