Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
pradnya_amol
Creator
Creator

Make Date with Year and Month combination

Hi All,

I have [Calendar Year / Month] Field available in QVD and want to do either:

 

1. wish to combine it with Master Calendar QVD so that I can get CalendarDate "OR"

2. directly create / make date out of [Calendar Year / Month] Field

Thanks in advance.

 

Pradnya Pampatwar.

 

 

Labels (1)
1 Solution

Accepted Solutions
pradnya_amol
Creator
Creator
Author

No. I have tried that but its not working.

View solution in original post

6 Replies
ajsjoshua
Specialist
Specialist

Hi,

 

Try this MakeDate(Year, Month, 1) as Date

 

Regards,

Joshua.

pradnya_amol
Creator
Creator
Author

No. I have tried that but its not working.

View solution in original post

ajsjoshua
Specialist
Specialist

Hi,

If [Calendar Year / Month] is a single field you can get year and month using subfield function and then you can makedate.

Try this

MakeDate(SUBFIELD( [Calendar Year / Month],'/',1), SUBFIELD( [Calendar Year / Month],'/',2), 1) as Date

 

Regards,

Joshua.

 

 

Vengatesh
Partner
Partner

Try Like this

MakeDate(Year([Calendar Year / Month]),Month([Calendar Year / Month]),1)

You Know What To Do.
pradnya_amol
Creator
Creator
Author

Hi Thanks for the reply. I tried but its coming like 1/1/2018 2/1/2018 till 12/1/2018 not getting all the Days like 1 to 30.

ajsjoshua
Specialist
Specialist

Dear Pradnya,

Load
MakeDate(SUBFIELD( [Calendar Year / Month],'/',1), SUBFIELD( [Calendar Year / Month],'/',2), 1) as Date
from
....


Create a master calendar and link the field Date to the master calendar.

MasterCalendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Date', recno()))-1 as mindate,
max(FieldValue('Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Date');


Now your Date field from master calendar will have all dates in a month.

Regards,
Joshua.