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 !

[Anonymous · Accepted solution]

Maybe something like :

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

[marcus_sommer]

I suggest to consider to split your calendar into a master-calendar and a master-timetable: How to use - Master-Calendar and Date-Values. Many things are easier to handle with them and you avoid the need to have a timestamp within your fact-table which could be by larger datasets increased the RAM footprint and slowed down the performance: The Importance Of Being Distinct.

- Marcus

[OmarBenSalem]

Thank you very much, this is exactly what I was searching for. but, it's not very accurate.

for instance, when I select 00:00 for example, the minutes affected are 0,1,2,3,4,5,6,7 , when I select 00:15, the minutes affected are from 7 to 22.

How could that be avoided?

00:00 will affect the minutes from 0 to 14, 00:15 from 15 to 29 and so on?

Thank you ! I'm really appreciative

[OmarBenSalem]

Yes thank you.

I've already considered that in a different project. But in this one , we're doing near real time analysis every 15 minutes and the comparison will only be btwn today and today-7.

Only one week to work within

[Anonymous]

You'll need to add an offset :

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

The + (7/1440) offset may need adjusting to suit your specific needs.

[marcus_sommer]

Another way would be:

pick(match(floor(minute(AddedTimeStamp)/15), 0,1,2,3, '00:00', '00:15', '00:30', '00:45')

- Marcus

[OmarBenSalem]

Yes, that went perfect ! Thank you very much Bill !

[OmarBenSalem]

Hi Bill,

When I use the expression of yours, I have for 8:00 ( the minutes from 7:45 to 8:00) activated; what I want to have is having 8:00 to 8:15 activated when I select 8:00.

Can you help me to adjust the expression to obtain such a result?

Thank you

[oknotsen]

The round() function has a built-in offset option. Add the offset you want as 3rd value of the function.

Round ‒ Qlik Sense

Ignore that the link it so Sense help; the function works 100% the same in QlikView.