Moving time bucket (example: time now - 2 hours)

[roisolberg]

Hello everyone,

I need to add to one of my models a counter that will count events in a few time buckets.

in the model i'd like to count closed lines in the system, that was closed in;

last 15 min

last 15 to last 30

30 min to an hour

1-2 hours

the thing is this is a dynamic report and from what i understand the time buckets are fixed hours/days

in a "freestyle " writing i need something like this:

count (ClosedLines) where ClosedLineDate=Today() and ClosedLineTime between Now() and (Now()-2 hours)

Thanks

[YoussefBelloum]

Hi,

would you be able to share your data structure ?

[roisolberg]

Hi Youssef,

thanks for your replay.

this is coming from a very big project, something in specific that may help you?

[balabhaskarqlik]

May be try like this:

Temp_Events:

LOAD * Inline

[

TIME,ZID

12:00:00 AM,1

01:00:00 AM,2

02:00:00 AM,3

03:00:00 AM,4

04:00:00 AM,5

05:00:00 AM,6

06:00:00 AM,7

07:00:00 AM,8

08:00:00 AM,9

09:00:00 AM,10

10:00:00 AM,11

11:00:00 AM,12

12:00:00 PM,13

01:00:00 PM,14

02:00:00 PM,15

03:00:00 PM,16

04:00:00 PM,17

05:00:00 PM,18

06:00:00 PM,19

07:00:00 PM,20

08:00:00 PM,21

09:00:00 PM,22

10:00:00 PM,23

11:00:00 PM,24

];

Events:

LOAD

  ZID,

  Time(TIME,'hh:mm:ss') as Time

Resident Temp_Events;

DROP Table Temp_Events;

Intervals:

LOAD

  %DLRO_KEY,

  Type,

  Arrival_Datetime,

  Time(Frac(Arrival_Datetime),'hh:mm:ss') as Arrival_Time,

  Depart_Datetime,

  Time(Frac(Depart_Datetime),'hh:mm:ss') as Depart_Time,

  Occupancy_Hour,

  Visits

FROM

[..\Desktop\TB04_20150627_222530.xls]

(biff, embedded labels, table is Sheet1$);

Join

IntervalMatch(Time)

LOAD

  Arrival_Time,

  Depart_Time

Resident Intervals;

[roisolberg]

Hi Bala,

this looks more appropriate for intervals,

i am looking for something that is not hard-coded, because the last 15 minutes for example depand on the time now.

is there something like Now()-2 hours?

[YoussefBelloum]

My mistake, I was asking for data structure which is not interesting here..what I need to know is rather the format of the field ClosedLineDate and how he is loaded on Qlik ?

[roisolberg]

LineClosedDate is in format 'DD/MM/YYYY'

LineClosedTime 'hh:mm:ss'

when asking how it is loaded to Qlik do you meen the Load kind?

this actually could be as a stand alone table, it does not have to be connected to the other data as i can pull everything from a single table in our DB (the lineID, Close date/time, status)

[YoussefBelloum]

Ok, I was asking how it was loaded to Qlik because It can be imported on a format (from the source) and using scripting functions you can re-format that field in another format (changing it or removing a crucial information from it) and depending on the format, some techniques works and others don't.. so instead of sticking a general solution, I prefer to propose a specific one everytime.

If was you, I would create a flag as a field, named 'time bucket' for example, created like this:

your_table:

load

.

if(Date#(ClosedLineDate,'DD/MM/YYYY')=today() and ( Timestamp#(ClosedLineTime,'hh:mm:ss') >= Timestamp(timestamp(now(),'hh:mm:ss')+(120/24/60),'hh:mm:ss') ), '1-2 hours') as Time_Bucket,

.

.

FROM...

[DavidŠtorek]

Hi,

for what you are requesting just use Now()-0,0833333333333333. Which returns you date and time before 2 hours . Maybe just convert it back to human readable date and time format if you need.

[DavidŠtorek]

You can use set analysis to count dynamicaly.

count ({1<ClosedLineDate={'=Today()'},ClosedLineTime={'=$(>=Frac(Now())-0,0833333333333333'}>}ClosedLines).

The same principle could be used for other time periods.