4 Replies Latest reply on Jul 11, 2016 11:05 PM by Pushpendra Nabh Tripathi

# Find a Min Value Over Last N Years

Greetings,

I have a dataset which is just two columns: a date, and a value for that date.  For example:

7/1/2016 1,542,151

6/24/2016 4,564,222

6/17/2016 9,654,123

What I am trying to do is find the minumum value for the 5 prior years for the same week number of each datapoint.

For example, on 7/1/2016, this was the 27th week of the year.  I am wanting to see the minimum value for the 27th week of 2011-2015.  My goal will be two lines displayed on the chart - the current value, and the smallest value in the Nth week of the last 5 years (excluding this year).

Any idea how to do this?

Thanks!

• ###### 1. Re: Find a Min Value Over Last N Years

I am sure there is a more elegant way of doing this...but here is one way.

I created a random data set.

Next, I created a calculated field "Min by week" using the following

{FIXED [Week] : min([Value])}

Then I created a chart to show an average value by week and the minimum value for that week regardless of the year. To simplify, I selected Year(date) as a filter.

Does this help?

• ###### 2. Re: Find a Min Value Over Last N Years

I will look into this and see if it fits what I'm trying to do.  Here's a graphic of what I'm working towards.  The min that I'm trying to calculate is the lower boundary of the gray range.  Next, I'm going to calculate the max.  Finally, I'm going to find a way to get Tableau to display a full year of data on the X axis, even though the data isn't available.  But the first step for me is to try and find this dynamic min.

I am having trouble loading the file because it is on a newer version of Tableau than I am currently running.

• ###### 3. Re: Find a Min Value Over Last N Years

Hi Barry,

To add to Naveen's answer, you'll need to create a calculated field for week.  You can do this using DATEPART('week',[YourDate]).  Naveen's formula will then give you the lowest value for every week # and you can get the top of your range by doing a MAX instead of MIN.

Regards,

Ivan

2 of 2 people found this helpful
• ###### 4. Re: Find a Min Value Over Last N Years

Hi Barry,

Attached is the .tbwx file which is in accordance to what Naveen and Ivan described.