topic Get simple month text (MMM) from date field in QlikView

Get simple month text (MMM) from date field

Thu, 16 Apr 2009 01:17:29 GMT

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?

Get simple month text (MMM) from date field

sna — Thu, 16 Apr 2009 01:23:39 GMT

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

Get simple month text (MMM) from date field

Thu, 16 Apr 2009 01:55:00 GMT

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.

Get simple month text (MMM) from date field

danielrozental — Thu, 16 Apr 2009 02:05:57 GMT

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,

Get simple month text (MMM) from date field

Thu, 16 Apr 2009 02:21:25 GMT

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?

Get simple month text (MMM) from date field

danielrozental — Thu, 16 Apr 2009 02:32:04 GMT

Try

DUAL(

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

num(month(DateField))) AS Month,

And then sort Month as number

Get simple month text (MMM) from date field

Thu, 16 Apr 2009 02:39:33 GMT

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.

Get simple month text (MMM) from date field

rwunderlich — Thu, 16 Apr 2009 02:49:23 GMT

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

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

-Rob

Get simple month text (MMM) from date field

Thu, 16 Apr 2009 03:17:27 GMT

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

Re: Get simple month text (MMM) from date field

johnreena — Thu, 20 Jan 2022 11:38:19 GMT

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