Convert numeric month to a text month

[Former Member]

hello,

i'd like to know if there is a way manually or using a function that will convert a numeric month  to text. for example if the month field has numbers 1 to 12, i want to display january if month field = 1, etc.

thank you

tracy

[former_member207052 · Accepted solution]

Sorry for my previous post.

In Lumira there are no formulas that you can use to convert a number to month Name (or nested conditions to hard-code look-up). If you have a date of course you can create a time hierarchy.

But if you don't, you can achieve this by following the below steps.

1. Create a new excel file with 2 columns (month number and name) and add it as a new data set
2. Go to prepare room and merge the two month numbers(figure 1)
3. If you drag and drop month name and your measures you should see the desired results. (figure 2)

[Former Member · Accepted solution]

hello,

i was able to follow your tutorial, but i still cannot display the month names across the bottom of the chart in calendar order (the first column should be january). they are displayed in alphabetical order (first column is april).

thank you

tracy

[ashutosh_rastogi · Accepted solution]

The only option I could think of was adding both numbers and month names to dimension, and then it would sort correctly, however it would look like below

Not sure if this helps.

Regards,

Ashutosh

[former_member207052 · Accepted solution]

If you are good with displaying month number in the chart, follow Ashutosh's idea.

For some reason, if you don't want to display month number or if you really want to convert the month text to actual month follow the below steps(And as a added benefit you can get rid of the dummy excel file where you had month names )

1. From your month number create a new calculated Dimension and save it (here I've called it "Month value") with formula
   1. AddMonthToDate(#1999-12-31 18:30:00.000+0000#, {Month Number})
2. Now click on the configuration wheel on the newly created dimension and select "create a time measure"
3. Time hierarchy will be created with year,Quarter, month and date. Now delete All except month.
4. Now if you use this month you will see the results as expected.

Hope this help!

[ashutosh_rastogi · Accepted solution]

Good, this works perfect

[Former Member · Accepted solution]

thank you ashutosh,

i will give it a try

tracy

[Former Member · Accepted solution]

i will try this also narashimman,

i never thought about deleting the hierarchy levels that i don't need. i think that's really clever.

thank you

tracy

[Former Member · Accepted solution]

Hi Narashimman,

I tried your suggestion of creating a dimension that uses the Add Month To Date function. The problem with this function is that it bumps up the month to the next month. By that I mean, if the date is Jan 2, 2015, the function returns Feb.

Tracy

[Former Member · Accepted solution]

hi ashutosh,

while this suggestion works, it just doesn't look so good, because i need to display other values across the bottom of the chart.

too bad, we can't hide fields on the chart.

thanks

tracy

[Former Member]

Hi

you can refer the following thread.This actually works!

http://scn.sap.com/thread/3659530

Regards

Megha