3 Replies Latest reply on Feb 25, 2016 8:20 PM by Shinichiro Murakami

# Create a rolling average Calculated field.

I currently have my data set up by month for 2014 and 2015. I have graphed 2015 using bars and I want to graph 2014 using two properties. I want a horizontal line representing the total at the end of 2014 (December) and would like that total to be calculated as the sum of the 12 months (numerators) divided by the sum of the 12 months (denominators). Essentially a rolling average every month ending in December.

Is this possible with tableau? currently What I've done is created a table manually in excel and just joined it in to get that line.

Thank you!

-Harnish

• ###### 1. Re: Create a rolling average Calculated field.

Definitely possible with Tableau. Almost anything is possible in Tableau

Please post a packaged workbook with your data or Superstore data, and I'm sure someone here would be willing to help you out.

-Steve

• ###### 2. Re: Create a rolling average Calculated field.

thank you! I've posted the workbook in: Create a rolling average Calculated field II

• ###### 3. Re: Create a rolling average Calculated field.

Harnish,

Use LOD to calculate 2014 Running Total.  //Overview: Level of Detail Expressions

To compare this year month to previous year month, you can not use "date" function because 2014 Dec and 2015 Dec is different thing.

You can use date name like January, February ,,,,,,,

but in this case, only alphabetical sort is available and cannot sort month order  correctly.

That's the reason I need to create "month header" and "year"

right(str(month([Date])+100),2)+"-"+datename('month',[Date])

[year]

year([Date])

[2014 Run_AVG]    //  int is needed to align dual axis (change data type to "int" from "float"

int({fixed :sum(if[year]=2014 then [Score] end)}/12)

[2015 monthly score]

if [year]=2015 then [Score] end

Put [2015 monthly score] to main Y axis and put  [2014 Run_AVG] to 2nd Y axis and synchronize axis.

Thanks,

Shin

9.0 attched

1 of 1 people found this helpful