Correct MONTHTODATE() for Gregorian calendar

gazwillott
Contributor

Correct MONTHTODATE() for Gregorian calendar

Hi,

 

Does anyone know how I can correct the MONTHTODATE() function when the model time is set to Weeks 5-4-4?

 

The function works perfectly apart from when the month changes during the week. With the current time settings, the second month cumulation starts earlier than it should because it is starting from the first day of the week in which the month has crossed over in rather than the 1st of the month.

 

Any help would be greatly appreciated.

 

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Correct MONTHTODATE() for Gregorian calendar

@gazwillott 

As @rob_marshall has alluded to you need to map days into month time. 

However, I would use a slightly different set of functions to populate the mapping.

ChrisHeathcote_1-1618992441357.png

 

You can then use MOVINGSUM to calculate MTD.

 

ChrisHeathcote_2-1618992527346.png

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

4 REPLIES 4
rob_marshall
Moderator

Re: Correct MONTHTODATE() for Gregorian calendar

@gazwillott 

 

This is what I have done in SYS Time module by day:

 

 

2021-04-20_12-01-42.png2021-04-20_12-01-02.png

 

Basically, I use 15 as the day because that will always be in the "correct" month.

 

Hope this helps,

 

Rob

ArunManickam
Master Anaplanner/Community Boss

Re: Correct MONTHTODATE() for Gregorian calendar

Hello @gazwillott ,

 

The function works alright in my case. I am not sure if you want the function to adopt the gregorian calendar, is it your expectation?

 

ArunManickam_0-1618970548219.png

 

THanks

Arun

 

ChrisHeathcote
Community Boss

Re: Correct MONTHTODATE() for Gregorian calendar

@gazwillott 

As @rob_marshall has alluded to you need to map days into month time. 

However, I would use a slightly different set of functions to populate the mapping.

ChrisHeathcote_1-1618992441357.png

 

You can then use MOVINGSUM to calculate MTD.

 

ChrisHeathcote_2-1618992527346.png

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

gazwillott
Contributor

Re: Correct MONTHTODATE() for Gregorian calendar

Amazing. Works a treat. Thanks very much 🙂