If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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
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)
would you be able to share your data structure ?
thanks for your replay.
this is coming from a very big project, something in specific that may help you?
May be try like this:
LOAD * Inline
Time(TIME,'hh:mm:ss') as Time
DROP Table Temp_Events;
Time(Frac(Arrival_Datetime),'hh:mm:ss') as Arrival_Time,
Time(Frac(Depart_Datetime),'hh:mm:ss') as Depart_Time,
(biff, embedded labels, table is Sheet1$);
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?
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 ?
LineClosedDate is in format 'DD/MM/YYYY'
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)
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:
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,
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.
You can use set analysis to count dynamicaly.
The same principle could be used for other time periods.