4 Replies Latest reply on Oct 4, 2017 10:54 AM by Andrew Connolly

# Display 5-year high value/5-year low

Hello! This seems like a very basic question but i am having difficulty adding this function to my report.

I generate a report of current economic trends on a weekly basis and I am intending to add on a simple table returning for example:

1) 5-year high value and date of occurrence

2) 5-year low value and date of occurrence

3) All time high and date of occurrence

4) All time low and date of occurrence.

I am probably looking at a 4x3 simple table that will return the values as i update them on a weekly basis.

Any experts can point me to the right formula? I am looking at the "IF sum(Sales) = window_max(sum(sales)) then max(order_Date)" function but am having difficulty putting it into use.

• ###### 1. Re: Display 5-year high value/5-year low

Hi Eric  - you were headed down the right path with window_max - the key is specifying what the window is! Check out the attached workbook to see an example. I've taken the simplest approach of putting each metric on its own sheet - you can form a table using containers on a dashboard.

WINDOW_MAX(SUM([Sales]),-51,0) is the key calc

Because I've placed week of order date on columns, I want to return the max sum(sales) over the previous 51 values, plus the current value.

The second calc returns any dates that have a sum(sales) value matching our calc from above (date should populate for every week where a 'change' occurred and there was a new max. Then the running max returns the most recent instance.

RUNNING_MAX(if SUM([Sales]) = [Calculation1] then attr(DATETRUNC('week', [Order Date])) END)

The final calc is the table calc LAST() - we put this on the filter shelf and set it equal to 0 so that we only get the column of data for the current week

-Andrew

• ###### 2. Re: Display 5-year high value/5-year low

Hi Andrew! Thanks for the reply. I am running Tableau 10.1 and cannot seem to open your twbx. Any chance to reupload another copy of it? Thanks!

• ###### 3. Re: Display 5-year high value/5-year low

Hi All,

I cannot seem to figure this problem out. Any form of advise of tip would be very much appreciated.

I have attached a workbook for better clarity at the spreadsheet i am trying to build. Thanks.

• ###### 4. Re: Display 5-year high value/5-year low

Hi Eric - sorry for the lengthy delay. Attached is a 10.1 version of the workbook. Take a look and please let me know if you have any questions.