
1. Re: Showing maximum and minimum with a calculated moving average
pooja.gandhi Apr 21, 2015 10:11 AM (in response to Clare Elliott)Hi Clare,
Welcome to the forums! Maybe you can set up your view to something like below? And set the labels to display min/max of your calculated field that will be on the view instead of CNT(Quantity) in my example? So for 2011, min value is 9 and max is 54.
You can place the weeks and your measure in your view and have the years on color on the marks card.

2. Re: Showing maximum and minimum with a calculated moving average
Clare Elliott Apr 22, 2015 5:51 AM (in response to pooja.gandhi)Hi Pooja
Many thanks for taking time out for help to answer my query. However, I don't think that is really the solution I'm looking for. I probably didn't explain what I want to do very well in the first place!
What I want to do is produce a chart that looks something like the one below:
So I want to aggregate the previous 4 years worth of data (and not show as individual years), average it for each week and then display it as a 3 week rolling average (which I've done) and also calculate and display the maximum rolling average and the minimum one too. That way it can be easily seen if the rolling average for this current year falls within the expected range as calculated from the previous 4 years worth of data.
Not sure if I've made this any clearer but any help on how to do this would be amazing!
Many thanks
Clare

3. Re: Showing maximum and minimum with a calculated moving average
Clare Elliott Apr 27, 2015 3:55 AM (in response to Clare Elliott)BUMP  can anyone help please??
Thank you

4. Re: Showing maximum and minimum with a calculated moving average
Esther Aller Aug 18, 2015 2:28 PM (in response to Clare Elliott)Hi Clare,
I am the Tableau Technical Support Specialist that assisted with this issue back in July. I saw from the survey that the provided solution worked, which is great! I thought I would go ahead and share the solution on the forum in case anyone else has the same issue.
CREATE THE CALCULATIONS
 Create a calculated field named "2015 Sales" with a formula similar to the following:
IF DATEPART( 'year', [Order Date] ) = 2015
THEN [Sales]
END  Create a calculated field named "Previous Sales" with a formula similar to the following:
IF DATEPART( 'year', [Order Date] ) != 2015
THEN [Sales]
END  Create a calculated field named "Moving Avg" with a formula similar to the following:
WINDOW_AVG( SUM( [!Previous Sales] ), 1, 1 )  Create a calculated field named "Min of Moving Avg" with a formula similar to the following:
WINDOW_MIN( [!Moving Avg] )  Create a calculated field named "Max of Moving Avg" with a formula similar to the following:
WINDOW_MAX( [!Moving Avg] )  Create a calculated field named "Avg of Moving Avg" with a formula similar to the following:
WINDOW_AVG( [!Moving Avg] )
BUILD THE VIEW
 Drag [Order Date] to the Columns shelf
 Rightclick on [Order Date] on the Columns shelf and select More > Week Number
 Drag another copy of [Order Date] from the data pane to Detail on the marks card
 Drag [2015 Sales] to the Rows shelf
 Rightclick on [2015 Sales] and select Quick Table Calculation > Moving Average
 Rightclick on [2015 Sales] again and select Edit Table Calculation
 In the Table Calculation dialog, set Previous Values and Next Values each to 1 and click OK
 Drag [Min of Moving Avg] over the yaxis until the cursor shows two green bars and drop it
 Rightclick on [Min of Moving Avg] and select Edit Table Calculation
 In the Table Calculation dialog choose Advanced for Compute using
 In the Advance dialog move Year of Order date over into the Addressing pane (on the right) and click OK
 Repeat steps 811 for [Max of Moving Avg] and [Avg of Moving Avg]

running average(1).twbx 1.5 MB
 Create a calculated field named "2015 Sales" with a formula similar to the following: