Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chesterluck
Creator II
Creator 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
Partner
Partner

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

View solution in original post

3 Replies
swuehl
MVP
MVP

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
Partner
Partner

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

View solution in original post

vivientexier
Partner
Partner

Does this solves your problem?

If yes kindly close the post by marking it answered.