4 Replies Latest reply on Jul 18, 2016 11:23 AM by nigora.sharipova

# Prior 4Wk Avg by Hour

Hi,

I am using the following formula to calculate prior 4 week average by hour.

(

lookup(sum([order]),-24*7)+

lookup(sum([order]),-24*14)+

lookup(sum([order]),-24*21)+

lookup(sum([order]),-24*28)

) /4

However, when i filter on a specific date, my 4 week average calculation does not work anymore and i only see actuals for the specified date.I tried to apply the following as a filter (lookup(min(([Date])),0)); however, it did not help.

Would greatly appreciate your suggestions.

Thanks!

• ###### 1. Re: Prior 4Wk Avg by Hour

Can you post a sample worksheet?

• ###### 2. Re: Prior 4Wk Avg by Hour

Hi Naveen,

Thank you for your response. I attached a sample workbook to my original post.

Tab 1: Original Date Filter - the 4 week average calculation is removed when the worksheet is filtered on a specific date

Tab 2: Date Filter based on (lookup(min(([Date])),0)) calculation. The filter works; however, i am not able to apply the filter to all worksheets.

Thank you again for your help!

• ###### 3. Re: Prior 4Wk Avg by Hour

It looks like you want to calculate a 4-week moving average for each data point on an hourly basis. One way of doing this would be to use a Table Calculation like this:

Make sure you select Hour for the date axis so that the Table calculation is performed over the last 672 hours for each data point. Not that you will not have any value for the moving average for the first 672 data points. If you choose to include the current value in the average, then you can change 672 to 671 in the dialog box.

This should work even when you select a filter for Product and Brd. I was able to generate the following viz which shows the Sum(Cnt) by Product on the left axis and the moving 4-week average on the right axis. Let me know if this is what you are looking for.

I am attaching the revised workbook.

• ###### 4. Re: Prior 4Wk Avg by Hour

Hi Naveen,

Thank you for your response.  The standard calculation in Tableau provides a moving 4wk avg, where I am looking specifically at the prior 4 wk avg by hour. For example, for the 7/11 9AM , I would like to calculate the average of:

7/4   9AM

6/27 9AM

6/20 9AM

6/13 9AM

I am able to get it with the formula i provided above; however, when i filter on the date my 4wk avg calculation gets removed.