Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

16 Replies
SunilChauhan
Champion
Champion

NO OF PERSONS AT SPECIFIED TIME

COUNT( DISTINCT IF(timeOut<=ENTERYOURTIME,id))

Sunil Chauhan
Not applicable
Author

Hi Sunil

what if there is a laps of days - ex. login 24.12.2011 time 2300 and logout 25.12.2011 time 0100 and I want who is in at midnight

I´ve attached a QV file with data

Not applicable
Author

From the attached file I need to know how many was "in" at all whole hours.

ex. at 1400 there were 20 present

     at 1500 there were 25 present

a barchart e.ex showing all 24 hours on the x axis showing the count people present at the different hours

and so on

Not applicable
Author

Hi,

Maybe this will help you for the first steps.

Regards vicky

Not applicable
Author

Hi vicky

thanx - but counting the timein only counts the people loggin in between 0900 and 0959.

I want the people that logged in at 0800 and logged out at 1200 were also present at 0900 to be a part of the count

Not applicable
Author

ex.

login   logout

0800   1200

0900   1000

0835    0900

0300    1000

all 4 logins were present at 0900

rohit214
Creator III
Creator III

hi

why dont you use COUNT(id)

may it useful for you

thanks rohit

Not applicable
Author

Hi rohit

look at my attachment - counting the ID doent do it

Not applicable
Author

Please everybody - there must be an answer to my problem.

Using intervalmatch sound like something usable - but I dont know how to?????