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

    Find year corresponding to max value

    Darin Friedrichs

      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

       

       

      StateYearSales
      California2012557
      California2013421
      California2014305
      Oregon2012125
      Oregon2013226
      Oregon2014605

       

       

      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
          Joe Oppelt

          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
            pooja.gandhi

            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
              Darin Friedrichs

              Awesome this works greats, thanks!

              • 4. Re: Find year corresponding to max value
                Darin Friedrichs

                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