8 Replies Latest reply on Sep 30, 2018 10:05 AM by Janet May

# Rolling 3 Months -- Doesn't Account for # of responses

Hi all,

I am working with customer satisfaction data, which is surveyed on a daily basis (some days we get more responses, other days we get less). I need to create a moving average score for the last 3 months.

I know I can do this using the table calculation function - however, when I look at my aggregate scores for the last 3 months - the numbers are slightly different. I know this is because of the way it is being calculated (the moving average score is using the average month to month, whereas the aggregate is taking into account ALL responses (i.e. when you hover over a column in excel and it averages that.

I need help on how to make it so that the moving average isn't calculated based on an average (i.e. average month score), but rather the average score of the total number of responses within those three months?

Thanks!

• ###### 1. Re: Rolling 3 Months -- Doesn't Account for # of responses

Hello Janet,

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?

• ###### 2. Re: Rolling 3 Months -- Doesn't Account for # of responses

Hi Robert,

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)

• ###### 3. Re: Rolling 3 Months -- Doesn't Account for # of responses

Janet,

How about looking into a Volume Weighted Moving Average, Volume Weighted Moving Average | Personal Criteria Formulas (PCF) | TC2000 Help Site

• ###### 4. Re: Rolling 3 Months -- Doesn't Account for # of responses

Hi Robert,

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.

• ###### 5. Re: Rolling 3 Months -- Doesn't Account for # of responses

Hi Janet

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

Jim

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.

• ###### 6. Re: Rolling 3 Months -- Doesn't Account for # of responses

Hi Jim,

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!

Cheers!