3 Replies Latest reply on Apr 24, 2012 8:08 AM by Jonathan Drummey

    12 Month Rolling Mean

    Andre Goehler

       

      Hello,

       

      I am trying to calculate a 12 Month Rolling Mean for satisfaction questions which can be answered with a number on a scale of 1 to 7.

      Now, obviously a table calculation or Window_AVG(avg([]),-11,0) respectively ignores the number of respondents. Its just an Average of Averages.

      Can anyone help me in writing an average of the last 12 Month Data for every month which includes the number of Records as a weight?

       

      Thanks in advance

       

      Andre

        • 1. Re: 12 Month Rolling Mean
          Jonathan Drummey

          Hello Andre,

           

          Here's one way to go about this, using nested table calculations. You're going to need Answer ID (the answer range from 1-7 in the sample data) and the Date in the view, to get the necessary level of detail for the calculations to work, I show this in the Working it Out Crosstab worksheet in the attached workbook.

           

          - Start with a Month of Date: DATETRUNC('month',[Date]). This makes partitioning easier.

          - Create a Weighted Answer: SUM([Number of Records]) * ATTR([Answer ID]).

          - Create an Avg Weighted Answer for Month: WINDOW_AVG([Weighted Answer]). This will have its Compute Using set to Advanced... with Compute using Answer ID, Sorted along Month of Date/Min/Ascending. (The sort isn't strictly necessary, but is a safety check in case you bring in or remove other dimensions that would change the sort in the view.

          -Create a Rolling 12 Month Average: WINDOW_AVG([Avg Weighted Answer per Month], -11, 0). This will have its Compute Using set to Month of Date (while the nested Avg Weighted Answer for Month still has its Compute Using set differently). The Rolling 12 Month Average returns the same value for every Answer ID/Month of Date combination, which is what we want. However, if we want to plot out the average on a chart over time, then we'd be getting overlapping marks.

          -So, the last step is to create a Rolling 12 Month Average for Chart: IF FIRST()==0 THEN [Rolling 12 Month Average] END. This has its nested Compute Using set to Answer ID, while the other two table calcs return their values. This returns only one value per Month of Date.

           

          With this all set, then you can duplicate the crosstab and turn it into a line chart, as in the Chart view in the attached.

           

          Let me know if this works for you!

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: 12 Month Rolling Mean
            Andre Goehler

            Hi Jonathan,

             

            Thank you for your response. Can you please give me a definition of the fields:

             

            Response ID

            Respondent ID

            Answer ID

             

            This will help me to understand your workings and replicate it in my data.

             

            Thanks

            • 3. Re: 12 Month Rolling Mean
              Jonathan Drummey

              Hi Andre,

               

              You're welcome!

               

              Response ID = unique ID assigned to each response

              Respondent ID = ID assigned to each person/entity who responds

              Answer ID = in this case, it's the answer value, from 1-7.

               

              When I work with survey data, I'll typically have those three fields, plus a Question ID that uniquely ID's the question.

               

              If you're not already familiar with table calculations, you're going to need to be very careful to duplicate the table calc settings, because the calcs require different compute using's to work at the proper levels of aggregation.

               

              Jonathan