topic Re: How to count how many people was/are in the building?? in QlikView

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

Fri, 11 Nov 2011 08:25:23 GMT

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

Re: How to count how many people was/are in the building??

SunilChauhan — Fri, 11 Nov 2011 08:39:59 GMT

NO OF PERSONS AT SPECIFIED TIME

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

Re: How to count how many people was/are in the building??

Fri, 11 Nov 2011 09:02:31 GMT

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

Re: How to count how many people was/are in the building??

Fri, 11 Nov 2011 09:17:33 GMT

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

Re: How to count how many people was/are in the building??

Fri, 11 Nov 2011 09:45:22 GMT

Hi,

Maybe this will help you for the first steps.

Regards vicky

Re: How to count how many people was/are in the building??

Fri, 11 Nov 2011 11:25:07 GMT

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

Re: How to count how many people was/are in the building??

Fri, 11 Nov 2011 11:27:38 GMT

ex.

0800 1200

0900 1000

0835 0900

0300 1000

all 4 logins were present at 0900

Re: How to count how many people was/are in the building??

rohit214 — Fri, 11 Nov 2011 11:37:08 GMT

why dont you use COUNT(id)

may it useful for you

thanks rohit

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

Fri, 11 Nov 2011 12:02:15 GMT

Hi rohit

look at my attachment - counting the ID doent do it

Re: How to count how many people was/are in the building??

Sat, 19 Nov 2011 18:33:29 GMT

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

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

Re: How to count how many people was/are in the building??

swuehl — Thu, 24 Nov 2011 12:31:52 GMT

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

Re: How to count how many people was/are in the building??

johnw — Mon, 28 Nov 2011 17:52:31 GMT

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.

Re: How to count how many people was/are in the building??

johnw — Mon, 28 Nov 2011 19:49:20 GMT

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.

Re: How to count how many people was/are in the building??

Tue, 29 Nov 2011 07:06:55 GMT

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?

Re: How to count how many people was/are in the building??

swuehl — Tue, 29 Nov 2011 17:41:14 GMT

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

Re: How to count how many people was/are in the building??

johnw — Tue, 29 Nov 2011 17:59:57 GMT

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.

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

Thu, 01 Dec 2011 13:18:49 GMT

Hi John

thanks for all your help - its what I needed