So I'm a little confused, about the month part. Are you showing a total of 12 months and want to go back three months for the moving average. Or are you actually only showing three months in total?
If you see the image above -- I have created the rolling calculation to use the previous three months. I.e. for Adam it is 60% in August which when you average the right part of the table ((69+61+50)/3) = 60%. Meaning the average it is calculating is based on an average (i.e. averaging all responses for that month and then taking the average of that).
My issue is, that what if in one month I only have 10 responses and in another I have 100? This calculation isn't taking into account the variation in response count/month.
WINDOW_AVG(AVG([Score]), -2, 0)
Thanks for sending this my way - I am a little confused about the denotation of it all.
I see here:
A 20 period simple volume weighted moving average of price can be written as follows.
AVG(C * V, 20) / AVGV20
Is similar to the goal I am trying to achieve. Where I want a 3 month period volume weighted moving average of score. I am just not sure how to write it out in syntax form.
I would like to see the workbook but in general if you are using the Moving Avg calculation it is just averaging the 3 month period
you could calculate the values using window_sum to total the numerator and again for the denominator (or window_count - can't tell without seeing your book)
using superstore data see below
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Thanks for this! I used this formula:
window_sum(SUM([Score]),-2,0)/WINDOW_SUM(COUNT([offerObjectId]),-2,0) to account for the rolling scores.
I had a hard time getting the numbers to match, but I realized its because I needed to add a couple of filters to the data set itself!
Glad to help out