Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
vengadeshpalani
Contributor

Master Calendar With Hour

hi all

i try to create a master calendar with Hour 

LET vMinDate = num(Timestamp(Timestamp#('05/05/2015 05:05:05','MM/DD/YYYY hh:mm:ss')));

LET vMaxDate = num(Timestamp(Timestamp#('05/06/2015 15:05:05','MM/DD/YYYY hh:mm:ss')));

LET vDays = vMaxDate - vMinDate ;

Calendar:

LOAD Year(KeyDate) as Year,

Date(MonthName(KeyDate),'MMM/YYYY') as MonthYear,

Month(KeyDate) as Month,

Day(KeyDate) as Day,

Date(Floor(Date(KeyDate,'MM/DD/YYYY')),'MM/DD/YYYY') as Date,

Hour(KeyDate) AS Hour,

Timestamp(Floor(KeyDate, 1/24),'MM/DD/YYYY HH') as KeyDate;

LOAD

    Timestamp($(vMinDate) + (RecNo()-1)/24) as KeyDate

AUTOGENERATE 24 * $(vDays);

Max date Max hour is 15 but Hour 15 is not associated with Date what i did wrong?

master cal.png

1 Solution

Accepted Solutions

Re: Master Calendar With Hour

I think you were adding one less hour

LET vMinDate = num(Timestamp(Timestamp#('05/05/2015 05:05:05','MM/DD/YYYY hh:mm:ss')));

LET vMaxDate = num(Timestamp(Timestamp#('05/06/2015 15:05:05','MM/DD/YYYY hh:mm:ss')));

LET vDays = vMaxDate - vMinDate ;

Calendar:

LOAD Year(KeyDate) as Year,

Date(MonthName(KeyDate),'MMM/YYYY') as MonthYear,

Month(KeyDate) as Month,

Day(KeyDate) as Day,

Date(Floor(Date(KeyDate,'MM/DD/YYYY')),'MM/DD/YYYY') as Date,

Hour(KeyDate) AS Hour,

Timestamp(Floor(KeyDate, 1/24),'MM/DD/YYYY HH') as KeyDate;

LOAD

    Timestamp($(vMinDate) + (RecNo()-1)/24) as KeyDate

AUTOGENERATE 24 * $(vDays) + 1;

3 Replies

Re: Master Calendar With Hour

Can you attach your qvw file?

vengadeshpalani
Contributor

Re: Master Calendar With Hour

PFA..

Re: Master Calendar With Hour

I think you were adding one less hour

LET vMinDate = num(Timestamp(Timestamp#('05/05/2015 05:05:05','MM/DD/YYYY hh:mm:ss')));

LET vMaxDate = num(Timestamp(Timestamp#('05/06/2015 15:05:05','MM/DD/YYYY hh:mm:ss')));

LET vDays = vMaxDate - vMinDate ;

Calendar:

LOAD Year(KeyDate) as Year,

Date(MonthName(KeyDate),'MMM/YYYY') as MonthYear,

Month(KeyDate) as Month,

Day(KeyDate) as Day,

Date(Floor(Date(KeyDate,'MM/DD/YYYY')),'MM/DD/YYYY') as Date,

Hour(KeyDate) AS Hour,

Timestamp(Floor(KeyDate, 1/24),'MM/DD/YYYY HH') as KeyDate;

LOAD

    Timestamp($(vMinDate) + (RecNo()-1)/24) as KeyDate

AUTOGENERATE 24 * $(vDays) + 1;