1 Reply Latest reply on Jul 15, 2016 7:26 AM by Shinichiro Murakami

    Creating a 5-Year Range Chart

    Barry Sheldon

      Greetings,

       

      I am attempting to create a 5-year range chart in Tableau and I am stumped.  A 5-year range chart shows the evolution of a data series through time versus the 5-year min, max, and average.  I've attached an example spreadsheet with my data series and how every calculation is performed.  Would anyone happen to know how to create a 5-year range chart in Tableau?

       

      Some key requirements for a 5-year range chart:

      - The X axis must show the begining and end of a year, so as more data is available, it "crawls" across the screen towards the right.

      - The 5-year min is equal to the minimum value for that specific week in the previous 5 years.  For 2016, the 5-year min for this week is the lowest value of the 28th week of 2011-2015.

      - The 5-year max is the same concept as the min.

       

      Thanks for the help!

        • 1. Re: Creating a 5-Year Range Chart
          Shinichiro Murakami

          I believe this is closest?

           

          I could not find the way to change the "range" to smooth area chart for now.....

           

          There are some technical limitations at this point with given Tableau feature.

          We cannot draw multiple lines/areas in 2nd axis.

          We cannot draw line and area chart in same axis.

           

          So, only the way to draw the "range" is using reference line.

           

           

          Formula.

           

          [Latest Date]

          {fixed:max([Date])}

           

          [week]

          datepart('week',[Date])

          [year]

          year([Date])

          [Range Filter]

          if year([Date])>= year([Latest Date])-6

          and year([Date])<=year([Latest Date])-1

          then "In" else "Out" end

           

          [5 year Max]

          {fixed [Week]:max( if [Range Filter] ="In" then [Value] end)}

          [5 year Min]

          {fixed [Week]:min( if [Range Filter] ="In" then [Value] end)}

          [5 year Avg]

          {fixed [Week]:avg( if [Range Filter] ="In" then [Value] end)}

          [Value the Current Year ]

          if [year]= year([Latest Date]) then [Value] END

          [Value the previous Year ]

          if [year]= year([Latest Date])-1 then [Value] END

           

           

           

          Thanks,

          Shin

           

          9.2 attached