Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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,
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
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.
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,
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?
Try
DUAL(
text(date(DateField,'MMM')),
num(month(DateField))) AS Month,
And then sort Month as number
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.
text(date(Datefield,'MMM') as Month
works for me when I select Numeric for the chart sort. Have you selected Numeric sort?
-Rob
Ok I tried it again using "text(date(Datefield,'MMM') as Month" and this time it sorted correctly. Everything looks good! Thanks guys!!