Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a Month field in my Table. And that Field contains values Like this = 'JANUARY', 'FEBRUARY' Like this format. In some of my calculation I am using Maximum Month. So I create a variable vMaxMonth = max(Month).
To Make the Month as Number values I used this piece of code in the script:
LOAD
if(capitalize(MONTH) = 'January', 1,
if(capitalize(MONTH) = 'February', 2,
if(capitalize(MONTH) = 'March', 3,
if(capitalize(MONTH) = 'April', 4,
if(capitalize(MONTH) = 'May', 5,
if(capitalize(MONTH) = 'June', 6,
if(capitalize(MONTH) = 'July', 7,
if(capitalize(MONTH) = 'August', 8,
if(capitalize(MONTH) = 'September', 9,
if(capitalize(MONTH) = 'October', 10,
if(capitalize(MONTH) = 'November', 11, 12))))))))))) as [Month],
But when I am showing month in the list box I have to show the Month Like this: Jan, Feb, Mar, Apr Like this.
I was also trying to use the INLINE to Make the Month ordered like this:
LOAD * INLINE [
Month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
];
But since the Month field is getting number value with the using of IF statement, INLINE month can not match with that.
Can anybody guide me how can i achieve above mentioned functionality from the script?
Thanks,
Sk
This will return the month in Dual format so in text format it will display the Short Month Name and in numeric format can be sorted.
=Date(MakeDate(2001, Month(Today())),'MMM')
You can substitute Today() with your actual date field.
You can try this:
Month(MakeDate(Year, Date#(Capitalize(MONTH), 'MMMM'))) as Month
Or maybe just
Month( Date#( Capitalize(MONTH),'MMMM')) as Month
Hi All,
I got a solution of this problem by Gysbert Wassenar in another forum in Qlik community. But thank you all for providing a solution for my problem.
Thanks a lot!
Cheers,
Sk
Awesome .
Please close this thread by marking it 'Assumed Answer' and mark for helpful answers, if any
Best,
Sunny
Hi,
Here is the work around for Month_Name.
Master_Calendar:
LOAD Date_Tmp as Event_Dates,
Week(Date_Tmp) as Week,
Year(Date_Tmp) as Year,
Capitalize(Month(Date_Tmp)) as Month,
Day(Date_Tmp) as Day,
WeekDay(Date_Tmp) as DayWeek
RESIDENT TmpCalendar;
INNER JOIN
Month_Tmp: //to ensure a correct "sort by load order"
LOAD * INLINE [
Month, Month_Name
1,Jan
2,Feb
3,Mar
4,Apr
5,May
6,Jun
7,Jul
8,Aug
9,Sep
10,Oct
11,Nov
12,Dec
];
DROP Table TmpCalendar;