How to count how many people were/are in the building??

[#1]

I´ve got a loginsystem/database that register login and logout with the following fields:

ID, dateIn, timeIn, dateOut, timeOut

I need to be able to find out how many was in the building at any given date or how manwas in the building

at ex. 1200 o´clock.
ex. at which date were the most people in the building and at what time.

I know its possible using SUMPRODUCT i a spreadsheet, but I would like to use QV

The problem is that people log in and out at different times and are present in a variable period

[swuehl]

Hi,

maybe like attached?

I used a while in the load to create a record per day, so if a LogIn - LogOut period spans across date boundery, you will get two or more records.

Please check the script for details.

Then I used a data island with half hour values to be used as x-axis in my chart. This is the same approach as in the other thread you have found.

Hope this helps,

Stefan

[johnw]

I've never really solved this to my satisfaction.  Intervalmatch (or doing the same with a while loop) tends to be how we solve these, but that isn't always practical.  If your users can login and logout at any time, multiple times per day, with those entries timestamped, there's no practial way to apply an intervalmatch or while loop.

I did have one partially-successful experiment.  I used +1 and -1 values for the two states.  Then, to determine your state at any point in time, sum the state values <= that point in time.  You could make this a chart, I think even with a continuous axis, by using a date island.  The attached example is by date, but I think even timestamps would work, displayed at whatever resolution you wanted.  You could try it, anyway.  Performance could be pretty bad, though, for large data sets.

I think this is an area of improvement for the QlikView product.  I've never been satisfied with its handling of date and time information.  I consider it quite poor in that regard.

[johnw]

Never mind my previous response.  I was trying to solve the problem for arbitrary times, but a chart won't be for arbitrary times.  If you only need to know who's there at whole hours, then an intervalmatch of timestamps to hours will do the trick.  Then you just count(distinct ID).  See attached.

[#5]

Hi John

thanks a lot for your help - It , as you say does the trick, but the solution Í´m trying to get is to display

the overall load of people in the buillding at a given time.

There are no people logging in more than once a day.

What I need to show is at a timerange from 00 to 23 when is the load highest - using the attached data - that is data from 1 year. At what time a day(anyday) is the load the highest.

And then I also need to find out what day of the attached data had the highest load?

[swuehl]

Hi polschou,

I think John's approach will also deliver these information, just replace the dimension of the chart with what you desire, hour(HOUR) or DATE for example (please note HOUR is a time stamp field, so I created a HourOfDay field in the script from that). I've attached a modified file for illustration (Sheet 1).

I noticed that both mine and John's solution will not take login periods that don't cross the Hour / half hour boundery into consideration, but that's by design, because we are looking at logged in ID at certain points in time only. With your sample data, we both miss a noticeable amount of events though, so maybe you want to decrease the intervals we are looking at.

Hope this helps,

Stefan

[johnw · Accepted solution]

polschou wrote:

but the solution Í´m trying to get is to displaythe overall load of people in the buillding at a given time.

The first example I posted could be adapted to give you the load of people in the building at any arbitrary time.  A better approach for your data would be to count IDs using set analysis based on the time in and time out and your arbitrary time, which would be better for your specific case.

count({<TIMESTAMPIN *={"<=$(vTimestamp)"}

       ,TIMESTAMPOUT*={">=$(vTimestamp)"}>} distinct ID)

The second example I posted gives you the load of people at any given hour. 

If you want something other than one of the above, I don't understand what you're asking for.

polschou wrote:

What I need to show is at a timerange from 00 to 23 when is the load highest - using the attached data - that is data from 1 year. At what time a day(anyday) is the load the highest.

Again, I'm not sure I understand.  The graph shows when the load was highest for the selected date.  What do you want, which hour was highest for the whole year?  OK, make the hour field be just the hour, and do your intervalmatch on a datehour field.  This isn't some advanced technique - if you need a field, add that field.  Or did you want some text box or something showing the hour with the highest load?

concat(aggr(if(rank(count(distinct ID),1,1)=1,HOUR),HOUR),',')

polschou wrote:

And then I also need to find out what day of the attached data had the highest load?

Make a chart by date and find the highest point, or sort by Y descending.  If you just want a single number, do it the same as hour:

concat(aggr(if(rank(count(distinct ID),1,1)=1,DATE),DATE),',')

I've cheated a little in that I've inner joined the intervalmatch back to your main table to get rid of records where they weren't in the building at the end of ANY hour, as then the null hour or date could have the highest count, and did for date.  This also breaks the set analysis above, since it no longer supports arbitrary times.  But I don't have any idea if this is what you're after, so there's only so far I want to go with this right now.

Example updated.

[#8]

Hi John

thanks for all your help - its what I needed