Finding the average

neg177
Regular Contributor

Finding the average

Hi,

 

Need help creating a formula that pick's up the moving average for a number within 6 business days. 

Example) The amount for a line item over the past Weekdays were 1, 4, 6, 8, 9. I want to forecast this by finding the average of those days. 

 

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Finding the average

@neg177 

The trick her is to manipulate the value for the days so that you replace the weekend value with the first two values of the moving 5 day average.

Therefore, as the moving average progresses the function includes the weekends but the values for Tues and Weds are posted forward into Sat and Sun so they are included in the movingsum.

ChrisHeathcote_0-1619079740823.png

 

Create a new line which I have called, 'Movingsum Value', and using WEEKDAYS to distinguish which day of the week post forward 4 days if the weekday is 6 or 7. This has the effect of replacing Sat and Sun with the values from Tue and Wed.

You can then use the MOVINGSUM function as standard to calculate a moving average.

ChrisHeathcote_1-1619079905049.png

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

6 REPLIES 6
chenjack.sonos
Certified Master Anaplanner

Re: Finding the average

Have you tried using MOVINGSUM? Contrary to the name, it can also be used to calculate the moving average if you indicate the aggregation method.

 

You can see more on the formula here: MOVINGSUM 

neg177
Regular Contributor

Re: Finding the average

Yes, we are using the Moving sum, but not getting back the correct average, I believe because it is picking up weekends (which we don't want it to do). This is the current formula we are using...

 

MOVINGSUM('Y03 Securitization (Actuals)'.Portfolio Factor Rate, -5, 0, AVERAGE)

 

we are trying to pick up the previous M, T, W, Th, F 

ArunManickam
Master Anaplanner/Community Boss

Re: Finding the average

Hello @neg177 ,

 

You could achieve it still using the movingsum. But do not use the parameter Average.

 

Sum and divide by the count.

 

Using if condition, sum only the weekdays (skip the weekends).

XX-Y03 Securitization (Actuals) = if weekend then 0 else Y03 Securitization (Actuals)

 

For eg, if the current day is monday, your moving sum should be

Average = MOVINGSUM('XX-Y03 Securitization (Actuals)'.Portfolio Factor Rate, -6, 0, SUM) / 6

 

But you divide it by 6, so you will skip the weekend and calculate your average.

 

Hope it helps. 

 

Thanks

Arun

neg177
Regular Contributor

Re: Finding the average

neg177_0-1619039666980.png

 

ChrisHeathcote
Community Boss

Re: Finding the average

@neg177 

The trick her is to manipulate the value for the days so that you replace the weekend value with the first two values of the moving 5 day average.

Therefore, as the moving average progresses the function includes the weekends but the values for Tues and Weds are posted forward into Sat and Sun so they are included in the movingsum.

ChrisHeathcote_0-1619079740823.png

 

Create a new line which I have called, 'Movingsum Value', and using WEEKDAYS to distinguish which day of the week post forward 4 days if the weekday is 6 or 7. This has the effect of replacing Sat and Sun with the values from Tue and Wed.

You can then use the MOVINGSUM function as standard to calculate a moving average.

ChrisHeathcote_1-1619079905049.png

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

neg177
Regular Contributor

Re: Finding the average

Hi,

 

Thank you it worked out great.