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
-
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,
3
Answers
-
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?
1 -
See if this helps
Step 1: Create a Fake Months List
Step 2: Create one Global module. Month from Native Time Scale line item is what you are interested in.
2 -
@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
1 -
@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.
1 -
1
-
0
-
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.
🙂
0 -
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?
0 -
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
0 -
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.
0 -
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
0 -
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.
0 -
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
0 -
If anyone wants to accomplish the opposite of the original question, which is to convert a month name to a month number, they can do so by MONTH() function.
This function takes a date or time period input and returns the numeric value of the month (e.g., January = 1, February = 2, etc.).
0
