Get simple month text (MMM) from date field

[#1]

Hey guys. I have a date field that I'm trying to create a date dimension for. I want to have a month dimension that's just the first 3 letters of the month based on the full date. I tried using the Month function, and it only returns an integer, not the text. I also tried using Date(DateField, 'MMM'), but that created a ton of duplicate entries, where all I want is a simple 12 month list so I can pivot by month. Any ideas?

[sna]

The reason you see duplicate numbers for one month is because you are just trying to foce it to display in MMM mode, but in the back end, it still recognizes as separate dates. You need to do something like...

Month(DateField) as Month

and make sure that your MonthNames variable set within the scripts contains the three letter acronyms. e.g.,

SET

MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

or try...

Date(Monthstart(Datefield), 'MMM') as Month

By doing this, you are forcing to get a single value for all dates within a month, and converting it to be displayed in a three letter month acronym.

I hope it works.
Shima

[#3]

Thanks for the reply Shima, but neither solution seems to work. I have the MonthNames variable set as you said, but Month(DateField) as Month still returns 1-12.

Also, since the date field spans multiple years, MonthStart will return the beginning of the month, but on different years, so we still have the same issue with multiple values for each month.

[danielrozental · Accepted solution]

Do you get the first 3 letters of the month when you do Date(Datefield,'MMM')?

Try doing text(date(Datefield,'MMM') as Month

Regards,

[#5]

Ok almost there! 🙂

So "text(date(Datefield,'MMM') as Month" did give me the unique MMM values I wanted, but now I need to be able to sort them by month not alphabetically. Is there an easy way to convert this to a dual value or something?

[danielrozental]

Try

DUAL(

text(date(DateField,'MMM')),

num(month(DateField))) AS Month,

And then sort Month as number

[#7]

You could also do

text(date(DateField,'MMM')) AS Month

in the script and then use

Max(DateField)

in an ascending expression sort on the month field above.

[rwunderlich]

text(date(Datefield,'MMM') as Month

works for me when I select Numeric for the chart sort. Have you selected Numeric sort?

-Rob

[#9]

Ok I tried it again using "text(date(Datefield,'MMM') as Month" and this time it sorted correctly. Everything looks good! Thanks guys!!

[johnreena]

Okay, I tried to use "text (history box, mmm") monthly "again", this time sorted correctly. Everything looks good. Thank you, my friend

BeeTV Apk

Movie Hd

MegaBox

Cinema HD