Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor II

Convert Date to Month-Year format

Hi All,

I have a date in my fact table in the format DD/MM/YYYY and I want to convert it to Year-Month format MMM-YYYY.  I will be using this new Month-Year field to aggregate my fact table by Month-Year like below.

New:

load *

resident fact

group by MonthYear;

Could you help me how to achieve it.

1 Solution

Accepted Solutions

Re: Convert Date to Month-Year format

I would throw in MonthStart in there to make sure that MonthYear value doesn't repeat in a list box because of day

date(MonthStart(date#(Datefield,'DD/MM/YYYY')),'MMM-YYYY')

View solution in original post

6 Replies
Highlighted
Honored Contributor II

Re: Convert Date to Month-Year format

try this:

date(date#(Datefield,'DD/MM/YYYY'),'MMM-YYYY') as Datefield

Highlighted
Contributor

Re: Convert Date to Month-Year format

Try this:

Month(Date) & '-' & (Year(Date) as YM

Re: Convert Date to Month-Year format

I would throw in MonthStart in there to make sure that MonthYear value doesn't repeat in a list box because of day

date(MonthStart(date#(Datefield,'DD/MM/YYYY')),'MMM-YYYY')

View solution in original post

Highlighted
Contributor II

Re: Convert Date to Month-Year format

Thanks everyone.

Just a final question.

I just checked that MonthName() function is also giving same result as MonthStart() for me. Could you throw some light on this.

Highlighted

Re: Convert Date to Month-Year format

It does give the same output and will be a shorter expression, but I have seen some issues with using MonthName in set analysis when you have to do >= or <= or > or < with it. I prefer to use Date(MonthStart()) because then you can its slightly easier to use. But it comes down to how you plan to use it

Highlighted
Contributor II

Re: Convert Date to Month-Year format

Thanks for clarity.