topic Re: Month + year concatenation mmm-yy in QlikView

Month + year concatenation mmm-yy

infock12 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Month + year concatenation mmm-yy

Anil_Babu_Samineni — Tue, 01 Nov 2016 11:29:40 GMT

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'

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

Re: Month + year concatenation mmm-yy

tresB — Tue, 01 Nov 2016 11:31:56 GMT

Try like:

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

Re: Month + year concatenation mmm-yy

its_anandrjs — Tue, 01 Nov 2016 11:55:47 GMT

Try this way also

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

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

Regards

Anand

Re: Month + year concatenation mmm-yy

gajapathy74 — Tue, 01 Nov 2016 12:00:43 GMT

Can you try this:

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

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

(you can use MonthEnd based on your need)

Re: Month + year concatenation mmm-yy

infock12 — Tue, 01 Nov 2016 13:26:23 GMT

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'

Re: Month + year concatenation mmm-yy

infock12 — Tue, 01 Nov 2016 13:29:20 GMT

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.

Re: Month + year concatenation mmm-yy

infock12 — Tue, 01 Nov 2016 13:31:04 GMT

Hi Gajapathy,

This works like a charm! Thank you very much.

Re: Month + year concatenation mmm-yy

infock12 — Tue, 01 Nov 2016 13:32:36 GMT

Hi Anand,

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

Re: Month + year concatenation mmm-yy

Anil_Babu_Samineni — Tue, 01 Nov 2016 13:42:15 GMT

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

Re: Month + year concatenation mmm-yy

infock12 — Wed, 02 Nov 2016 17:03:03 GMT

Hi Anil,

Sorry for the late response. It does work and I have marked it as a correct answer.

Many thanks,

Karthik