4 Replies Latest reply on Jul 10, 2018 2:30 PM by Richard Garcia

# 5 Day Past Rolling Avg

I am trying to calculate past 5 Day Rolling Average but struggling to work out these details.  There are different channels within subset of data.  My goals is to create a summary by Channel past rolling 5 day average.  I tried in the Data Source tab Field Calculation with window_avg and table calculation moving avg but still struggle.

The result should look like for 6/13/2018 :

 By Channel Summary 5 Day Rolling Avg. a b 7.4 4.8

Here is the sample table data:

 Date Channel Count 27-May c 5 28-May c 4 28-May e 40 29-May b 3 30-May a 15 31-May c 9 1-Jun a 5 2-Jun b 6 3-Jun c 8 3-Jun e 8 4-Jun a 9 5-Jun b 6 6-Jun b 5 6-Jun e 20 7-Jun a 2 8-Jun b 3 9-Jun c 7 10-Jun b 4 11-Jun c 5 12-Jun a 6
• ###### 1. Re: 5 Day Past Rolling Avg

Do you have this in your data?

• ###### 2. Re: 5 Day Past Rolling Avg

This is the intended results last 5 days by all Channels

 Date A B 6/13/2018 7.4 4.8
• ###### 3. Re: 5 Day Past Rolling Avg

Hi Richard

The issue here is that you want the average of last 5 (max) values - not days (note there may be fewer than 5 values in a partition

so the straight forward table calc will not work

see the attached approach

I put in a counter

then' did a running sum on the counter

then the avg is

it will return this

it is a table calc and I hide the top part of the table

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.

• ###### 4. Re: 5 Day Past Rolling Avg

This is helpful and good...it calculates the rolling 5 day. Now i am trying to backfill old data but just repeats current day avg.  I dont know how you figured to approach this but it works

1 of 1 people found this helpful