topic Master calendar with hour in QlikView

Master calendar with hour

Anonymous — Tue, 12 Feb 2013 08:19:02 GMT

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;

Re: Master calendar with hour

swuehl — Tue, 12 Feb 2013 09:04:24 GMT

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

Re: Master calendar with hour

vivientexier — Tue, 12 Feb 2013 09:13:47 GMT

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".

Re: Master calendar with hour

vivientexier — Mon, 18 Feb 2013 14:14:59 GMT

Does this solves your problem?

If yes kindly close the post by marking it answered.