6 Replies Latest reply: Dec 11, 2015 4:05 AM by Mario Santos Serrano

# Moving Averages in Charts

Hi, i am new and am wondering how to add a moving average line in my chart.

I have a Bar chart showing me the number of people in an area over several days. so the Y-axis is the no. of people and the X-axis is the date.

I wish to have a line showing me a three day moving average of the number of people.

Once again, i am new and do not know where i should start.

• ###### Re: Moving Averages in Charts

Hi,

I've attached an example of an average and a 3 day rolling average. The example's based on daily sales, but you should be able to apply it to your situtation (instead of sales by date, you're analyzing number of people by date).

Please have a look, hope it helps.

Best,

Matt

• ###### Re: Moving Averages in Charts

I'm sorry but I am unable to open the file as I'm not using a paid version. Is there another way to explain what you have done?

• ###### Re: Moving Averages in Charts

Hi,

You can see the set analysis I've used to calculate the average and rolling average in the header of each column in the attached excel. I'll paste them below as well:

Average - avg(total sales)

3 Day Rolling Average - rangeavg(above(sum(sales),0,3))

Hope that helps.

Matt

• ###### Re: Moving Averages in Charts

Thanks, this was a useful start for me too.

An additional issue I had was how to prevent the average from plotting in future periods when I had data plotting beyond the current week number down to the zero line. Now the formula below is specific to the data I was plotting but anyone can probably get the generic gist of it, so FWIW for others.

If (weeknumber<=Week (Date (Num (Today ()))),Rangeavg (Above (Sum ({<isoYY ={'2012'}>}mQty ),0,5)),Null ())

weeknumber is the plot dimension

5 is for a 5 week rolling average in my particular case

the isoYY and mQty is the input formula for the data I wanted to average.

• ###### Re: Moving Averages in Charts

Thanks so much for the info. I have managed to get it to work using your formula. I was trying it out with after() previously. Can i check how does the rangeavg() work in tandem with above()?

After using the moving average, I am going to try out for a weighted average.

• ###### Re: Moving Averages in Charts

It is a good point, but I think that the best solution is pre-calculate the rolling average in the script.

You can find below my solution:

for i = 1 to 3

Join(RAW_DATA)

ID,

Date(Floor(Date + 7*\$(i)), 'DD/MM/YYYY') as Date,

Data as Data_PW\$(i),

Resident RAW_DATA;

NEXT

FINAL_DATA:

ID,

Date,

Data,

RangeAvg(Data, Data_PW1, Data_PW2, Data_PW3) as RollingAverage,

Resident

RAW_DATA

;

DROP Table RAW_DATA;

This is for a 4 week rolling average, but you can change the number of iterations in the the loop and the RangeAvg() function.

If you need a month rolling average you must use the AddMonths() function.

If you need an accumulate, you must change the RangeAvg() function by the RangeSum() function.

I hope it would be useful!!!!