People logged at each hour - Qlik Community

Report Inappropriate Content · ‎2013-09-13

Hey,

The problem I have is related to counting people currently logged in.

I have data base with several tags with the information, if user is logged in, logged, out, logged out temporarily or logged out permanently during a workday.

What i need to know is if user have been logged in at certain hour.

Datamodel:

User Status Time

1 3 2013-02-12 08:00:02

1 4 2013-02-12 09:00:07

1 5 2013-02-12 10:17:03

1 6 2013-02-12 15:12:05

2 3 2013-02-12 08:00:01

2 6 2013-02-12 08:00:01

2 3 2013-02-12 08:00:01

2 5 2013-02-12 08:00:01

2 6 2013-02-12 08:00:01

Im trying to find out which users have been logged in and for how many minutes every hour.

Status 3 = Log in

Status 4 = Temporarily logged out

Status 5 = Log in for temporarily logged out status

Status 6 = Log out

In the example i would like to show that, for example User 1 has been logged in at clock 8, 60 minutes.

User 1 has been logged out 09:00:07 - 10:17:03 = 67 minutes. Im trying to generate a chart telling how many active minutes there is per day.

So I would be able to tell that how many resources (minutes) certain user has been logged in or out every hour.

Im thinkin about if i need to generate status for every minute and for every state. Or then using some intervalmatch instead.

Is there someone who has been working with similar issues before and have working solutions ?

/Matt

marcus_sommer · ‎2013-09-13

In a chart you could calculate like this: if(Status = 4, Time) - if(Status = 3, Time). It depends of your layout (dimensions) and selections if these expression need more advanced parts like aggr() and/or set analysis in which it is embedded.

Also you could this calculate in load with a nested peek()-statement or with splitting the table and join or map your data together.

In any case I would recommend to split your timestamp-field in a date- and a time-field - it saved by larger amounts of data enormous RAM and performance. Also you should create a hour-field within the load for an easier way to select the data in GUI.

- Marcus

Report Inappropriate Content · ‎2013-09-16

Thank you for the answer.

Ive been going through similar post including Intervalmatch and peek statements, but havent found a working solution.

The main problem is that i would need ti interval several states to previous one and then be able to tell how many minutes each user has been online each hour. Most of the existing post includes one Login time and logout time each day.

Counting the total logged in time is possible but then matching it to the hours each day is still a mystery.

/Matt

marcus_sommer · ‎2013-09-16

See the attachment for two different approaches to calculate the login-time. The real case is surely more complicated but I think you could another requirements similar append or extend.

- Marcus