2 Replies Latest reply on Mar 23, 2017 12:29 PM by Michael Murphy

    Moving 4 quarter Average not using Window Calculations

    Michael Murphy
    Michael Murphy Mar 23, 2017 6:25 AM

      All,

       

      Thank you in advance! I am attempting to create a moving average calculation that is not limited to the data on the screen (i.e. Window Sum/Avg). For example, I would like each point of the line in the workbook attached to be a 4 quarter calculation (more below).

       

      In the workbook attached, I am recreating my issue with Superstore data. Let's pretend I would like to have a moving 4 quarter average for the percentage of total sales in the "West" region. In the viz attached I did this using window sum...

      WINDOW_SUM(SUM([West Record]),-3,0)

      /

      WINDOW_SUM(SUM([Number of Records]),-3,0)

       

      I would like for the first data point (Q1 2016) to also be a calculation of the previous 4 quarters of data even though that data is not displayed in the view. Currently Q1 2016 is just the % of west records in that quarter. This is why I mentioned the Window calcs won't work for me here. Can I do this with calculated fields? If so, PLEASE HELP!!!!! Thank you again!

      • 526 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Moving 4 quarter Average not using Window Calculations
          Michel Caissie
          Michel Caissie Mar 23, 2017 10:55 AM (in response to Michael Murphy)

          Michael,

           

          Check in the attached, if you get the expected result.

           

          The trick here is to put a calculated field on the filter shelf. This changes Tableau's order of operations. The table calculations are now done before the filtering.

          (Tableau needs to bring all the data in the engine in order to be able to compute the filter, the side effect being that all other table calculations are now computing on all the data and not only the data in the view after filtering).

           

          You can accomplish this by replacing Order Date  by  LOOKUP(min([Order Date]), 0 ). (the aggregation could be min,max or attr , it doesn't care it's computed on every row). This table calculation simply returns the Order Date.  The drawback is that you cannot use the range filter no more,  but you can find alternate ways to accomplish this using a parameter.

           

          In the attached, the final filter is

          DATEDIFF('quarter', LOOKUP(min([Order Date]), 0 ) , MIN( [Order Date (dataset last date)] ) ) < [show last x Quarters]

          where  [Order Date (dataset last date)] is  {MAX ([Order Date])}.

          and  [show last x Quarters] is a parameter.

           

          I also change the columns pill to discrete , to get an axis a bit less confusing.

           

          Michel

          2 of 2 people found this helpful