1 Reply Latest reply on Jan 16, 2012 12:47 PM by Joe Mako

    Moving averages

    Alexander Wright

      I'm trying to create a calculation that tracks the moving average of a metric by week. The challenge I have is that I want to be able to compare years so I have a line for each year. As a result, the moving average calculation restarts at the beginning of each year, rather than remaining continuous over  years. So, for instance, in the first week of January each year I just get the actual value as there are (as far as Tableau knows) no previous values.

       

      Is there any way around this?

        • 1. Re: Moving averages
          Joe Mako

          What I hear you asking for is something not intuitive in Tableau.

           

          Attached is a workbook with two tabs, first the default that I think you are able to create but is not the result you want.

           

          The second is what I think you are looking for, and uses some advanced Tableau concepts to create the view. Here is a brief rundown of what I did:

          1. created a calculated field to truncate the date value to the week, and this becomes the level of detail of interest:

           

          DATETRUNC('week',[Date])


          2. created another calculated field to get the year of the week, for coloring and separating the lines:

           

          YEAR([Week])


          3. created a calculated field to get the week number, normalizing the weeks:

           

          DATEPART('week',[Week])


          4. arranged the pills as in the attached view.

           

          Here is some explanation of why I have the pills how they are:

          - Week is our level of detail of interest, so is is a dimension - All Values (non-aggregated, and could be continuous or discrete)

          - Year is also a non-aggregated dimension because we want the lines to restart, and a discrete because I wanted categorical colors, you can make it continuous to get a gradient of colors.

          - Week Number is aggregated and set to continuous to get a nice axis, and aggregated because it is not needed for partitioning or addressing.

          - The table calculation uses an Advanced compute using because we need to address on multiple dimensions, so it does not partition or restart each year.

           

          Depending on your situation other factors may need to be taken into consideration.