Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
surajap123
Creator II
Creator 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
sunny_talwar

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
Frank_Hartmann
Master II
Master II

try this:

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

jrepucci15
Creator
Creator

Try this:

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

sunny_talwar

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

surajap123
Creator II
Creator II
Author

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.

sunny_talwar

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

surajap123
Creator II
Creator II
Author

Thanks for clarity.