Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
NO OF PERSONS AT SPECIFIED TIME
COUNT( DISTINCT IF(timeOut<=ENTERYOURTIME,id))
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
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
Hi,
Maybe this will help you for the first steps.
Regards vicky
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
ex.
login logout
0800 1200
0900 1000
0835 0900
0300 1000
all 4 logins were present at 0900
hi
why dont you use COUNT(id)
may it useful for you
thanks rohit
Hi rohit
look at my attachment - counting the ID doent do it
Please everybody - there must be an answer to my problem.
Using intervalmatch sound like something usable - but I dont know how to?????