4 Replies Latest reply on Aug 18, 2015 2:28 PM by Esther Aller

# Showing maximum and minimum with a calculated moving average

Hi

I'm new to Tableau and would really appreciate if someone could just help me with a query I have about how to do something! It's probably really simple but I'm really stuck and so any pointers would be great please.

I have a dataset which gives the number of laboratory confirmed organisms per week from 2011 until now. Ideally I would like to show this data as a chart with the 3 week moving average (1 before-current-1after) of the number of notifications for this year by week overlaid on the 3 week rolling average per week for the combined data for the previous 4 years.

So far, so good - I have managed to do this by calculating the weekly average for 2011-2014 using SUM([Number of Records])/COUNTD([Year No]) and then doing a quick table calculation (moving average) on this - so in effect I have an average of the average. This all seems ok and the numbers correspond to what I calculate using excel etc.

However, this is the bit that's stumping me! I also want to show the maximum and minimum range of my calculated average....so to illustrate with some data:

week 666518242
week 759587350
week 851666364
Average 58.658.372.652

Therefore from the above:

For week 7 - the three week rolling average is 58.6+58.3+72.6+52/4=60.4 (and this is what Tableau gives me...whoopee)

However, I would also like to display the minimum of the averages (52) and the maximum (72.6) on either side of the moving average line (for all the weeks obviously). I thought that using the quick calculation-moving calculation-minimum (or maximum) might be the solution but the numbers are wrong (and that would be far too easy!).

So please, if anyone could help me do this I would be very, very grateful!

Many thanks and here's hoping

Clare

• ###### 1. Re: Showing maximum and minimum with a calculated moving average

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

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

BUMP - can anyone help please??

Thank you

• ###### 4. Re: Showing maximum and minimum with a calculated moving average

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

1. Create a calculated field named "2015 Sales" with a formula similar to the following:

IF DATEPART( 'year', [Order Date] ) = 2015
THEN [Sales]
END

2. Create a calculated field named "Previous Sales" with a formula similar to the following:

IF DATEPART( 'year', [Order Date] ) != 2015
THEN [Sales]
END

3. Create a calculated field named "Moving Avg" with a formula similar to the following:

WINDOW_AVG( SUM( [!Previous Sales] ), -1, 1 )

4. Create a calculated field named "Min of Moving Avg" with a formula similar to the following:

WINDOW_MIN( [!Moving Avg] )

5. Create a calculated field named "Max of Moving Avg" with a formula similar to the following:

WINDOW_MAX( [!Moving Avg] )

6. Create a calculated field named "Avg of Moving Avg" with a formula similar to the following:

WINDOW_AVG( [!Moving Avg] )

BUILD THE VIEW

1. Drag [Order Date] to the Columns shelf
2. Right-click on [Order Date] on the Columns shelf and select More > Week Number
3. Drag another copy of [Order Date] from the data pane to Detail on the marks card
4. Drag [2015 Sales] to the Rows shelf
5. Right-click on [2015 Sales] and select Quick Table Calculation > Moving Average
6. Right-click on [2015 Sales] again and select Edit Table Calculation
7. In the Table Calculation dialog, set Previous Values and Next Values each to 1 and click OK
8. Drag [Min of Moving Avg] over the y-axis until the cursor shows two green bars and drop it
9. Right-click on [Min of Moving Avg] and select Edit Table Calculation
10. In the Table Calculation dialog choose Advanced for Compute using
11. In the Advance dialog move Year of Order date over into the Addressing pane (on the right) and click OK
12. Repeat steps 8-11 for [Max of Moving Avg] and [Avg of Moving Avg]
1 of 1 people found this helpful