Count all events in a time slot - LogFile analyses

[Steinardo]

Hi

I am quite new to Qlik Sense and run into a problem what i can't solve by myself. Maybe you can help me by pushing me in the right direction. 

I have a log file what contain a event number and a time stamp. The following code is simplified.

myDataTable:
Load * Inline [
event_number, event_date
1,2019-01-01 12:00:30
2,2019-01-01 12:00:31
3,2019-01-01 12:00:32
1,2019-01-01 12:01:30
2,2019-01-01 12:01:25
3,2019-01-01 12:01:35
1,2019-01-01 12:02:30
2,2019-01-01 12:02:40
3,2019-01-01 12:02:20
1,2019-01-01 12:03:30
2,2019-01-01 12:03:15
3,2019-01-01 12:03:45
1,2019-01-01 12:04:00
2,2019-01-01 12:04:00
3,2019-01-01 12:04:00
];

Load
Num([event_number]) AS [event_number],
Timestamp#([event_date], 'YYYY-MM-DD hh:mm:ss') AS [event_date]

RESIDENT [myDataTable];
DROP TABLE [myDataTable];

I have three variables lets call them vEvent, vBefore & vAfter.

I would like to have a bar chart with the dimension event_number and the measure count of all event_number in a time slot defined in the variables vBefore & vAfter for the event number defined in vEvent.

For example

vEvent = 1

vBefore = 10 sec

vAfter = 5 sec

the result should show/count all events in following time slots

2019-01-01 12:00:30, + 5 sec, - 10 sec 

2019-01-01 12:01:30, + 5 sec, - 10 sec 

2019-01-01 12:02:30, + 5 sec, - 10 sec 

2019-01-01 12:03:30, + 5 sec, - 10 sec 

2019-01-01 12:04:00, + 5 sec, - 10 sec 

I hope you understand what i try to archive.

Thanks for your support

Oliver

[Wlad_Masi]

The first 3 records should be counted as a single event date correct? Should it be the lowest one?
In my opinion you could try working with it in your data model, when loading the data.
Use a if statement where you check if the record is in the interval you want. In case it is, you can use the lowest time stamp to round up the records:

EX:
event_number, event_date, new_Field
1,2019-01-01 12:00:30,2019-01-01 12:00:30
2,2019-01-01 12:00:31,2019-01-01 12:00:30
3,2019-01-01 12:00:32,2019-01-01 12:00:30

After adjusting the data model, you can create a simple chart using new field as dimension and count of event_number as measure.

[marcus_sommer]

I think I would split the timestamp into a date- and a time-field and associate them with a master-calendar and a master time-table (beside the traditional time-fields it may also contain some time-buckets) and then the user could just select the needed time-frames. For the most scenarios this should be a quite effective way to provide an analysis.

If you really need such asynchron and customizable time-frames controlled through variables I would add a recno() within the time-table to get a continuous second-counter and then using it within a set analysis like:

count({< SecondCounter = {">=$(=max(SecondCounter)-$(vBefore))
                                                          <=$(=max(SecondCounter)+$(vAfter))"}>} Event)

Useful background to the above mentioned approaches could you find here: How-to-use-Master-Calendar-and-Date-Values.

- Marcus

[Steinardo]

HI

Very interesting stuff. I have read and understand (at least I think so) about master calendar and master time table. But i still did not archive what i want. I do not understand how to connect the set analysis to the event number. 

So i tried to modify your set analysis with following 

count({< event_date = {">=$(=max(Sum({$<event_number={2}>}event_date))-$(vBefore))<=$(=max(Sum({$<event_number={2}>}event_date))+$(vAfter))"}>} event_number)

Since 

Sum({$<event_number={$(vEvent)}>}event_date)

returned the date as an integer i hoped to arrive my goal. But the set analysis count every event.

[marcus_sommer]

Did you split the timestamp into a date and a time and associates them to the master calendar/timetable and creates in them the mentioned buckets and counter? If so then just play with count(event) and a few listboxes with year/month/date and hour/minute/second and the buckets/counter to see how it work and to validate your datamodel and the results. If it's clear you could extend it with some filter like:

event_number={2}

or ignoring a few

date=

It's important to understand it before going further with the more advanced set analysis with expressions and/or variables.

In your approach you used a sum() of event_date which could never return a valid value to compare it against the event_date again - further you nested two aggregations: max() and sum() without the use of aggr() which isn't a valid syntax and won't be useful in your case.

Therefore start with the beginning and go further step by step until you reached my suggestion. Depending on your real requirements you may apply an adjusted or even quite different approach but nevertheless it will be quite useful for you to decide how to implement your task.

- Marcus