TimeCalendar qlikview (grouping by 15 minutes intervals)

[OmarBenSalem]

Hi folks,

I've created a time Calendar but now, I want to be able to analyse and compare what's going on each 15 minutes interval.

For example,

I could have a bar chart with :

- hours as a dimension, and see what happens each hour.

- Minutes as a dimension and see what happens each minute

what I want also, is having each 15 minutes as a dimension:

something like this for example :

08:00-08:15    = 500$

08:15-08:30  = 380$

what should I do to do so?

Here is my calendar:

MinMaxDates:

LOAD Floor(Min(Timestamp#(Date_Timestamp, 'DD/MM/YYYY hh:mm:ss'))) as MinDate,

Floor(Max(Timestamp#(Date_Timestamp, 'DD/MM/YYYY hh:mm:ss'))) as MaxDate

Resident Ticket;

LET vMinDate= FieldValue('MinDate',1);

LET vMaxDate= FieldValue('MaxDate',1);

DROP Table MinMaxDates;

CalendarTemp: 

LOAD DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))) AS AddedDate, // Use this one as date  

     TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1)) AS AddedTimeStamp 

AUTOGENERATE 86399 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));  

//AutoGenerate

//$(vMaxDate) -$(vMinDate)+1;

Calendar:

LOAD AddedTimeStamp AS Date_Timestamp,  

    Year(AddedDate) AS MyYear,

  Month(AddedDate) AS MyMonth,

  Day(AddedDate) AS MyDay,

  Hour(AddedTimeStamp) AS MyHour,

  Minute(AddedTimeStamp) AS MyMinute,

   // Minute(AddedTimeStamp)*15 AS MyQuarter (this doesn't solve the issue, obviously)

  Second(AddedTimeStamp) AS MySecond

RESIDENT CalendarTemp

ORDER BY AddedTimeStamp ASC;

DROP TABLE CalendarTemp;

Thank you !

[OmarBenSalem]

Thank you Onno but still, I've tried but couldn't figure out a way to use the round function accurately to have the result I'm willing to have..

[oknotsen]

There are 1440 (24*60) minutes in a day.

You want to move the rounding point by 7.5 minutes. In other words 7.5/1440.

[OmarBenSalem]

actually from

    time(round( frac(AddedTimeStamp + (7/1440) ), 1/24/4) , 'hh:mm')as min15

to

time(round( frac(AddedTimeStamp + (7.5/1440) ), 1/24/4) , 'hh:mm')as min15

didn't change a thing:

8:00 still activate the minutes from 7:45 to 7:59 while I want it to activate 8:00 to 8:15

ps: thank you for the reply

[OmarBenSalem]

actually, -8/1440 will do it

[jagan]

Hi,

Check this link

Master Calendar with every 30 Minutes

For 15 Minutes (Just change 48 to 96)

LET vMinDate = Num(MakeDate(2014,1,1));  // Calendar Start Date

LET vMaxDate = Num(Today()); // Calendar End Date

LET vDays = vMaxDate - vMinDate + 2;  // Calculating number of days between Start & End Dates

Calendar:

LOAD Date(Floor(TimeStamp)) AS Date,

TimeStamp,

Hour(TimeStamp) AS Hour,

Minute(TimeStamp) AS Minute;

LOAD

    Timestamp($(vMinDate) + (RecNo() - 1)/96) as TimeStamp

AUTOGENERATE 96 * $(vDays);

Hope this helps you.

Regards,

jagan.

[OmarBenSalem]

Hi all,

I have yet another 2 issues:

1) when I select the quarter of an hour 09:15 for example I have the minutes activated : 15 to 30 while It should only affect the minutes 15 to 29 (15 minutes).

How could I change that ?

2) As I said, selecting the quarter of an hour 9:00 will affect 9:00 to 9:15; when I filter with the Hour 9:00,it should then affect the quarters :9:00(from minute 0 to 15(should be 14); 9:15(15 to 30); 9:30(30 to 45) and 9:45 (45 to 00) but in instead the selection is also activating the 10:00 !

why? And what to change to fix these 2 issues?

I'll be really appreciating any kind of help; I'm really in need ! thank you all !

Here is my time calendar:

MinMaxDates:

LOAD Floor(Min(Timestamp#(Date_Timestamp, 'DD/MM/YYYY hh:mm:ss'))) as MinDate,

Floor(Max(Timestamp#(Date_Timestamp, 'DD/MM/YYYY hh:mm:ss'))) as MaxDate

Resident Ticket;

LET vMinDate= FieldValue('MinDate',1);

LET vMaxDate= FieldValue('MaxDate',1);

DROP Table MinMaxDates;

CalendarTemp: 

LOAD DayStart(TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1))) AS AddedDate, // Use this one as date 

    TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1),'hh:mm') AS AddedTimeStamp 

AUTOGENERATE 86400 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate)); 

Calendar:

LOAD AddedTimeStamp AS Date_Timestamp, 

AddedDate,

    Year(AddedDate) AS MyYear,

  Month(AddedDate) AS MyMonth,

  Day(AddedDate) AS MyDay,

  time(round(frac(AddedTimeStamp + (-45/1440) ), (1/24/4)*4) , 'hh:mm')as MyHour,

  Hour(AddedTimeStamp) AS MyHour2,

  Minute(AddedTimeStamp) AS MyMinute,

    time(round( frac(AddedTimeStamp + (-8/1440) ), 1/24/4) , 'hh:mm')as min15

RESIDENT CalendarTemp

ORDER BY AddedTimeStamp ASC;

let vMinDate=peek('AddedDate',0, 'Calendar');

let vMaxDate=peek('AddedDate',-2, 'Calendar');

let vDayMax=day('$(vMaxDate)');

let vDayMin=day('$(vMinDate)');

NoConcatenate

Calendar2:

LOAD *

Resident Calendar

where AddedDate= date('$(vMinDate)','DD/MM/YYYY') or AddedDate= date('$(vMaxDate)','DD/MM/YYYY') ;

DROP Table Calendar;

DROP TABLE CalendarTemp;

LET vLastUpdatedDate=now();

[OmarBenSalem]

I found it out;

here's what You have to do :

time(class(timestamp#(AddedTimeStamp,'hh:mm'),MakeTime(0,15)), 'hh:mm')  as min15

[pgriffiths]

The Round function did not work correctly for me and gave some unexpected results. I think Floor is better for this. Love the use of Frac. 😄

Time(Floor(Frac(AddedTimeStamp), 1/24/4), 'hh:mm:ss') as 15min,