9 Replies Latest reply on Jun 13, 2017 9:47 AM by Hari Ankem

# Window average using parameter

Hi All,

I'm having an issue specifying a window average that I was hoping somebody could help me with.  I have weekly sales data for departments (each department may have multiple rows) where the week in the view is indicated by a parameter (the calculation is: if [week] = [week parameter] then [sales] end).  I'd like to add a column that averages the data for the week specified by the parameter and the previous three weeks.

I've attached a sample workbook to illustrate the problem.  Any help is much appreciated.  Thank you!

chris

• ###### 1. Re: Window average using parameter

Hi Chris

I am on a different version of tableau but

Have you tried      WINDOW_AVG(sum([Sales]),-3,0)

Let me know if that helps

Jim

• ###### 2. Re: Window average using parameter

You can create a week filter as shown to filter the data for the required weeks.

Or update your calculated field as shown below:

Here is the output using the calculated field. The filter has not been applied.

1 of 1 people found this helpful
• ###### 3. Re: Window average using parameter

And while I have only summed the values above, you can change it to average please.

• ###### 4. Re: Window average using parameter

Thanks, Jim.  Unfortunately the window average values computed by this formula don't change when I change the value of the parameter.

• ###### 5. Re: Window average using parameter

Hi Hari,

This will work dynamically, unfortunately the average is not correct.  I believe it's averaging the individual rows.

chris

• ###### 6. Re: Window average using parameter

OK. I have shown 2 averages below. Which one of them is correct? If neither, please share your expected result, and I can see how to get that.

• ###### 7. Re: Window average using parameter

Hi, Hari.  So what I am looking for in a 4 week average would be the sum of the sales for the current week, as specified by [Week Parameter], and the previous three weeks divided by four .  So if the week parameter is set at 6/12/17 then department 1 should yield 622.8175 / 4 = 155.7044 , department 2 should yield 578.1455 / 4 = 144.5364, and department 2 should yield 658.2014 / 4 = 164.5504.

Right now I have :

IF [Week] >= DATEADD('week',-3,[Week Parameter]) AND [Week]<= [Week Parameter]

THEN [Sales] / 4

ELSE 0 END

This yields the correct results if 4 weeks of data are available.  However, if I set the week parameter to the first, second, or third week in the data set then [Sales] should be being divided by 1, 2, or 3 respectively and not 4.  So I guess now the question becomes, what do we replace the denominator in "THEN [Sales] / 4" with to make this work dynamically?

Thanks again for all of your help, Hari!

chris

• ###### 8. Re: Window average using parameter

Hi Hari,

I figured it out using an LOD calculation.  In case you are wondering, here is what I did:

THEN [Sales]  /  {include [Week] >= DATEADD('week',-3,[Week Parameter]) AND [Week]<= [Week Parameter]: COUNTD([Week])}

This will count only those weeks that are included in the IF/THEN statement.

chris

• ###### 9. Re: Window average using parameter

Great. You can also do without LOD's and by adding a filter I mentioned previously and with the below formula for the weekly average.