Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
infock12
Creator III
Creator III

Month + year concatenation mmm-yy

Hi all,

I have a date field and would like to get in month year format. I tried the below. I have the attendance date stored as '2016-07-12 00:00:00.000' and I would like it to show 'mmm-yy'

Month(Attendance_Date) & '-' & Year(Attendance_Date) as 'MonthYear'

but I get load error. I tried other things too by looking at other comments but i still get the same error. Any help is appreciated.

Thanks

1 Solution

Accepted Solutions
gajapathy74
Creator II
Creator II

Can you try this:

1. Date(MonthEnd(Floor(Attendance_Date)),'MMM-YYYY') AS MonthYear

OR

2. Date(Floor(Attendance_Date),'MMM-YYYY') AS MonthYear

(you can use MonthEnd based on your need)

View solution in original post

10 Replies
Anil_Babu_Samineni

Do you have Attendance_Date fiels, If so try like below

Before moving, Try this

Load *, Date(Attendance_Date) as Sttendance_Year;

Load *, Attendance_Date FROM ......

Month(Attendance_Date,'MMM') & '-' & Year(Attendance_Date,'YY') as 'MonthYear'


OR


Month(Date(Attendance_Date),'MMM') & '-' & Year(Date(Attendance_Date),'YY') as 'MonthYear'

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
tresesco
MVP
MVP

Try like:

Date(Date#(Attendance_Date, 'YYYY-MM-DD hh:mm:ss.ffff'),'MMM-YY') As  MonthYear

its_anandrjs

Try this way also

Month(Attendance_Date) & '-' & Right( Year(Attendance_Date) ,2) as 'MonthYear'

Or

Date(MakeDate(Year(Attendance_Date), Month(Attendance_Date)) ,'MMM-YY') as MonthName

Regards

Anand

gajapathy74
Creator II
Creator II

Can you try this:

1. Date(MonthEnd(Floor(Attendance_Date)),'MMM-YYYY') AS MonthYear

OR

2. Date(Floor(Attendance_Date),'MMM-YYYY') AS MonthYear

(you can use MonthEnd based on your need)

infock12
Creator III
Creator III
Author

Hi Anil,

Thanks for the reply. Yes, I do have the Attendance_Date field. I tried both the functions but it is still giving me a load error. Do I have to do anything before the original Load statement? The first two lines you mentioned above, i.e., Load*, Date (Attendance_Date) as Atendance_Year;, and the next line. I am not sure what it is. My original script starts like below and I tried the two functions below.

Load:

Attendance_Date

Field 2,

Field 3,

Month(Attendance_Date,'MMM') & '-' & Year(Attendance_Date,'YY') as 'MonthYear'

Month(Date(Attendance_Date),'MMM') & '-' & Year(Date(Attendance_Date),'YY') as 'MonthYear'


infock12
Creator III
Creator III
Author

Thanks for the response Tresesco. This doesn't throw any error message but it gives blank results. Basically, no data in this field. When I create a list box with this dimension, it just has the heading but nothing in the list box.

infock12
Creator III
Creator III
Author

Hi Gajapathy,

This works like a charm! Thank you very much.

infock12
Creator III
Creator III
Author

Hi Anand,

Thank you for the quick response. This works brilliantly too! Thanks again!

Anil_Babu_Samineni

Karthik, Does GR Solution Works to you?

Then close this Thread by flag Correct Answer or else share your application so that we can help you

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful