Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chesterluck
Contributor II

Master calendar with hour

Hello

i'm using a master calendar on the basis of days. I want to have a finer granularity and be able to present aggr values on basis 'hour'.

A similar issue was already discussed here : http://community.qlik.com/message/106585#106585

What I've already prepared is a timestamp in the correct format:

 

Timestamp(alt(num#(Schichtdatum,'#','.',','),Timestamp#(Schichtdatum, 'DD.MM.YYYY hh'))) as Schichtdatum_Stunde_alt

Please give me a hint how to go on!

Here is the code im using to connect days:

 

MaxMinDate:

load
num(min(Schichtdatum)) as MinDate,
num(max(Schichtdatum)) as MaxDate

 
resident Auftragsdaten_BDE;


let varMinDate = peek('MinDate');
let varMaxDate = peek('MaxDate');

Temp_Calendar:

Load
$(varMinDate) + RowNo() - 1 AS DateNumber, 
Date($(varMinDate) + RowNo() - 1) AS TempDate 
AUTOGENERATE 1 
WHILE $(varMinDate)+IterNo()-1 <= $(varMaxDate);


Calendar:
LOAD
date(TempDate, '$(DateFormat)') AS Schichtdatum,
date(TempDate, '$(DateFormat)') AS CalDate,
Weekday(TempDate) AS WeekDay,
Week(TempDate) AS Week,
Day(TempDate) AS Day,
Month(TempDate) AS Month,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Year(TempDate) AS Year,
Week(TempDate)&'-'&Year(TempDate) AS CWeekYear,
Date(monthstart(TempDate), 'MMM-YY') AS MonthYear,
'Q' & ceil(month(TempDate) / 3)&'-'&Year(TempDate) AS QuarterYear


RESIDENT Temp_Calendar ORDER BY TempDate ASC;

DROP TABLE Temp_Calendar;

1 Solution

Accepted Solutions
vivientexier
Contributor II

Re: Master calendar with hour

Hello,

In order to create your calendar it is better to use numeric format than date format.

Today is day 41317. Tomorrow will be 41318. It means that 1 hour = 1/24

So your Autogenerate should be [ ($(varMaxDate) - $(varMinDate) + 1)*24 ] => number of hours between this two dates.

Then, instead of adding only => "+ RowNo()" you should add => "+ RowNo()/24".

3 Replies
MVP
MVP

Re: Master calendar with hour

chesterluck,

the referenced thread should already give you an idea of how to do this, just replace the magic numbers for seconds (24*60*60 resp 24/60/60) with the magic number for hours (24) in a day.

In addition, when comparing floating point numbers (that's what you want to do when linking your hour based calendar to your timestamp), make sure that both numbers are created / rounded the same way. Please check

http://qlikviewnotes.blogspot.de/2011/10/correct-time-arithmetic.html

Regards,

Stefan

vivientexier
Contributor II

Re: Master calendar with hour

Hello,

In order to create your calendar it is better to use numeric format than date format.

Today is day 41317. Tomorrow will be 41318. It means that 1 hour = 1/24

So your Autogenerate should be [ ($(varMaxDate) - $(varMinDate) + 1)*24 ] => number of hours between this two dates.

Then, instead of adding only => "+ RowNo()" you should add => "+ RowNo()/24".

vivientexier
Contributor II

Re: Master calendar with hour

Does this solves your problem?

If yes kindly close the post by marking it answered.

Community Browser