Convert month number to month (month period)

Hi,

 

I am looking for a way to convert a month number (f.e. 1) to month name (f.e. 'Jan 20'). 

How would you do this? 

 

Kind regards,

Catheline 

Best Answer

  • paolovm
    Answer ✓

    Hi @Catheline 

     

    Your question leaves the YEAR definition open in my opinion. 

     

    Other than that I guess an easy solution would be to: 

    1. Transform the number into a date (i left between quotes what could be harcoded) using the DATE function as DATE("01" , input , "YYYY)

    ->  outcome "01"/1/"YYYY"

    2. Use the period function on the date  

     

    Let me know if I misunderstood anything or my answer is not clear.

     

    Regards,

     

Answers

  • @Catheline 

     

    It surely is achievable but it would be great if you can post some screenshot on what exactly are you trying to do? Do you have any global module? Are you using Native timescale? Number 1 will be considered as Jan in all the years, are you furnishing Year number as well anywhere?

     

  • @Catheline 

     

    See if this helps

     

    Step 1: Create a Fake Months List 

    Misbah_0-1591360066962.png

    Step 2: Create one Global module. Month from Native Time Scale line item is what you are interested in.

    Misbah_1-1591360110686.png

    Misbah_2-1591360131594.png

     

  • @Misbah ,

     

    Flip your list around, instead of having Jan, Feb, Mar, etc as the code, have that as the name and the code be 1, 2, 3, 4, 5, etc.

     

    Rob

  • @rob_marshall  I thought of doing so but @Catheline  needs to input 1-12 numbers in a cell and system should derive months based on those numbers. If I flip it I think it gets complicated. 

  • @Misbah 

     

    I get what you are saying, but you do a finditem() on the number to render the month, no?

  • @rob_marshall 

     

    Ah I see what you saying!! 

  • Hi @paolovm,

     

    I like this idea!

     

    It avoids having to make an additional list.

     

    Thank you! 

  • Hi @Catheline 

     

    If that works as you'd expect appreciate if you could mark the "Accept as Solution" field so others can refer to this discussion in future.

     

    🙂

  • Hi,

    I have similar scenario, however, the month number will be based off a flag in the source system. 

    For example, we are getting a file from the source system which has bench_mnth which is a date - for example march 2021, jan 2021 etc. Based on that month, Month1, Month2, and Month3 would be mapped to later month. For example, if the bench_mnth is March 2021, then month1, month2, month3 would be Apr, May, June 2021.

    Any suggestions?

  • @nibandha 

     

    I guess I am confused as the way your Bench Month column in your attachment is perfect as Anaplan can read the 01 or the 03 (the month piece) as the actual month (Jan or Mar).  Or, is Bench Month more of an attribute or metadata?  If so, that can go in a systems module as a line item.

     

    Rob

  • @rob_marshall 

    Thanks for the quick response. 

    The bench month is not an issue as we can find out the month based on the rows, for example, 202103 is March. However, the amount for MTH01, MTH02,MTH03, would go to Months based on the Bench month. This is where my concern is. Of the two rows I have, the first one would go to APRIL, MAY, JUNE, however, the second one would go to FEB, MAR, APR.

    Any suggestion on how I can achieve it. Thanks.

  • @nibandha 

     

    The only thing I can think of is to load the data for Month 1, 2, and 3 into line items on a module which is dimensioned by Time (using the Bench MTH).  Then, you can create the true transformation in Anaplan.  Question about CC and GEO, are those one to one meaning the CC is tied to only one GEO?  If so, then have your list in the module be the CC.

     

    Rob

  • CC and GEO are not one-to-one. It is basically many to many, meaning Cone CC can have data against multiple and vice versa.

  • @nibandha 

     

    If that is the case, then concatenate the CC and GEO (I would do GEO_CC) and have that as the "transactional" list.  Then your module would be:

     

    Lists: your transactional list and Time (Month)

    Line items: Month 1, Month 2, Month 3

     

    Now, you will know what month the data begins in because you will use Bench MTH as which month Month 1 -> 3 will be populated.

     

    Rob