Correct MONTHTODATE() for Gregorian calendar

gazwillott
gazwillott
in Modeling

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,

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @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

     

Answers

  • rob_marshall
    rob_marshall

    @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
    ArunManickam

    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

     

  • gazwillott
    gazwillott

    Amazing. Works a treat. Thanks very much 🙂

Categories