Make Date with Year and Month combination

[pradnya_amol]

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.

[ajsjoshua]

Hi,

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

Regards,

Joshua.

[pradnya_amol · Accepted solution]

No. I have tried that but its not working.

[ajsjoshua]

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]

Try Like this

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

[pradnya_amol]

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]

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.