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!
rolling mean for survey.twbx.zip 85.7 KB
Thank you for your response. Can you please give me a definition of the fields:
This will help me to understand your workings and replicate it in my data.
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.