3 Replies Latest reply on Jan 16, 2014 10:26 AM by Andrew Ball

Moving (rolling) average by date, not rows

Hello all, long time lurker, first time poster.

I am trying to do something that I am not sure is possible in Tableau, and have not seen anyone else ask about much and.or solve.

I have a large set respondent-level survey data I am working with. Each response has a specific date it is associated with, and each date has one or multiple responses (rows), or none in some cases (no responses collected those days. I want to do a moving average of the responses..easy right?  Not so fast!  I do NOT want the average based on the rows, rather moving based on the DATE.  I.e., I want Tableau to look at all responses for the current date and a defined number of DAYS behind it and average all the encompassed responses.

Attached are two files:

1. Excel file with a sample of the raw response data in one sheet, and another sheet with an example of what I am trying to accomplish.
2. A Tableau file the the beformentioned data.

Any help would be greatly appreciated!

I would even be willing to pay for some consultation to solve this.

Thanks all!

• 1. Re: Moving (rolling) average by date, not rows

You could do this by using a scaffold data source with all the relevant dates in.

I'll try to put something together to show this, but don't worry - a solution is definitely possible.

• 2. Re: Moving (rolling) average by date, not rows

I'd be interested in seeing that, thank you.

• 3. Re: Moving (rolling) average by date, not rows

Hi Lars,

Finally got time to have a look at this. The solution is going to be easier than I thought, but I have a couple more questions:

1. Did you want a 14-day average, as your excel formula is only 13 days. I'm assuming you want that day & the previous 13?
2. Clarification is needed on what you want as an average. Is it:
1. The overall average score for the past 14 days (e.g. Day 1, result is 90/100 = 90%, Day 14, result is 1/2 = 50%, average is 91/102 ~ 90%)  - Weighted Average
2. The average of the daily average score (e.g. Day 1, result is 90/100 = 90%, Day 14, result is 1/2 = 50%, average is average of 90% and 50% = 70%) - Average daily score

Both are fine, but I'll put the right one together if you let me know.

Andrew