topic Re: Convert Date to Month-Year format in QlikView

Convert Date to Month-Year format

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

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.

Re: Convert Date to Month-Year format

Frank_Hartmann — Thu, 06 Apr 2017 14:33:53 GMT

try this:

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

Re: Convert Date to Month-Year format

jrepucci15 — Thu, 06 Apr 2017 14:35:02 GMT

Try this:

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

Re: Convert Date to Month-Year format

sunny_talwar — Thu, 06 Apr 2017 14:35:10 GMT

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')

Re: Convert Date to Month-Year format

surajap123 — Thu, 06 Apr 2017 14:57:27 GMT

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.

Re: Convert Date to Month-Year format

sunny_talwar — Thu, 06 Apr 2017 15:05:05 GMT

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

Re: Convert Date to Month-Year format

surajap123 — Thu, 06 Apr 2017 15:14:36 GMT

Thanks for clarity.