topic Re: Moving average calculation - single data point error in App Development

Moving average calculation - single data point error

tomelmslie — Sat, 16 Nov 2024 06:44:37 GMT

Hi,

I have monthly sales data and want to calculate a 12-months moving average, see data and formula below. Everything works fine, apart from one month (Feb’2017) which is incorrect. The correct value should be 505.3846153, it is 529.8333333 instead.

Please notice that I only want to include Sales where ‘SalesDate’ is null or it matches the reporting date. My formula is therefore as follows:

RangeAvg(Above(Count(IF(IsNull(SalesDate)or(SalesDate=ReportingDate),'sold')),0,12))

The table below shows the result of the Count – this works fine when I do it in isolation, so I assume the problem is not there.

What am I doing wrong? Any help is appreciated.

Thanks,

Tom

ReportingMonth	Sales	Sales Moving Average
2015-08	217	217
2015-09	214	215.5
2015-10	214	215.3333333
2015-11	214	215
2015-12	214	215
2016-01	212	214.6666667
2016-02	212	214.2857143
2016-03	202	213.25
2016-04	463	241.2222222
2016-05	531	270.2
2016-06	526	294.4545455
2016-07	542	315.1666667
2016-08	539	342
2016-09	591	373.5
2016-10	585	404.8333333
2016-11	584	436.0833333
2016-12	585	467.0833333
2017-01	579	497.5833333
2017-02	598	529.8333333
2017-03	409	550.75
2017-04	452	549.75
2017-05	453	543.5
2017-06	454	537.25
2017-07	455	529.9166667
2017-08	453	522.9166667
2017-09	459	512.0833333
2017-10	454	500.8333333
2017-11	453	489.6666667
2017-12	444	478.0833333
2018-01	446	467.0833333
2018-02	444	454.1666667
2018-03	418	452.4166667
2018-04	388	447.1666667
2018-05	392	441.8333333
2018-06	389	435.9166667
2018-07	390	430.5833333
2018-08	484	433.0833333
2018-09	466	434.1666667
2018-10	467	435.5833333
2018-11	467	436.8333333
2018-12	458	438

Re: Moving average calculation - single data point error

sunny_talwar — Mon, 28 Jan 2019 12:29:44 GMT

Would you be able to share a sample where we can see the issue?

Re: Moving average calculation - single data point error

tomelmslie — Mon, 28 Jan 2019 12:41:00 GMT

I am unable to share my app - however the table above and formula is exported from my app...

Re: Moving average calculation - single data point error

sunny_talwar — Mon, 28 Jan 2019 12:43:38 GMT

But the table above is an already aggregated data... If I use this data, I might get the right result, but we won't know the underlying problem.

Re: Moving average calculation - single data point error

tomelmslie — Mon, 28 Jan 2019 12:47:37 GMT

Would you be able to share any proposed solutions with me, then I will check to see if it works and feed-back?

Re: Moving average calculation - single data point error

sunny_talwar — Mon, 28 Jan 2019 12:59:41 GMT

Based on what I am seeing... the only thing I can ask you to check is to try this expression

RangeAvg(Above(Sum(If(IsNull(SalesDate) or SalesDate = ReportingDate, 1, 0)), 0, 12))

If this doesn't work then I have no idea because I don't know anything about the underlying data.