topic Re: Counting the number of people in the room in QlikView

Counting the number of people in the room

arixooo123 — Tue, 10 Apr 2018 01:16:00 GMT

I am trying to count the number of people in the room but my expressions not working:

My data set looks like:

Date In Time In Date Out time Out Id -- Hour In (I added this column for dimension)

2018-01-01 14:30 2018-01-01 19:30 123 14

Bar chart :

Dimension: Hour In

Expression: Count( {< [Time In] = {"<= $(Time Out) "} >} Id)

This expression does not work, even if it did since I didn't consider the date in and out I am not sure the result was 100% correct.

In some cases the Time in is close to midnight and Time Out is the day after. the Id should be counted in every hours that fall into between In and Out.

Thanks

Re: Counting the number of people in the room

Anil_Babu_Samineni — Tue, 10 Apr 2018 01:29:19 GMT

You consider Time Out is the variable? So, you can think about this?

Count( {< [Time In] = {"=[Time In]<= [Time Out] "} >} Id)

Re: Counting the number of people in the room

arixooo123 — Tue, 10 Apr 2018 01:41:22 GMT

Hi Anil

Thanks for your response

Would it be correct when the time in is for example 23:15 and time out is 2:30 (the day after )?

Re: Counting the number of people in the room

Anil_Babu_Samineni — Tue, 10 Apr 2018 01:55:24 GMT

Yup. But still when you have time slot from next date as time in. You can think about little more. Either you can through to digging that using date = {$(=max(date))}

Re: Counting the number of people in the room

arixooo123 — Tue, 10 Apr 2018 01:59:12 GMT

I tried your formula,

as long as both TimeIn and TimeOut happen in the same day it's returning the correct result, but for those which DateOut>DateIn it doesn't

Isn't there any way to implement something like case in sql?

Count ( { <

case when DateIn=DateOut then [Time In] = {"=[Time In]<= [Time Out] "} }

else

[Time In] = {"=[Time In]<= [Time Out] + Hour(24:00) "}

Id)

Re: Counting the number of people in the room

arixooo123 — Tue, 10 Apr 2018 02:31:06 GMT

I wrote this, the result sees correct. But I am not sure yet..I should check more. but in the meantime what do you think:

Sum({<[Date In]=[Date Out],[Time In] ={"=[Time In]<= [Time Out]"} >}ID)

Sum({<[Date In]-=[Date Out],[Time In] ={"=[Time In]<= ([Time Out]+ time(24,'HH'))"}>}ID)

Re: Counting the number of people in the room

Anil_Babu_Samineni — Tue, 10 Apr 2018 02:46:40 GMT

When you say result is correct? Then you may proceed to use this. But, Not entirely sure How set analysis works here in red part.. Are you really sure about correct results? If you remove Red part and even check the result will same

ar eiuiew wrote:

I wrote this, the result sees correct. But I am not sure yet..I should check more. but in the meantime what do you think:

Sum({<[Date In]=[Date Out],[Time In] ={"=[Time In]<= [Time Out]"} >}ID)

+

Sum({<[Date In]-=[Date Out],[Time In] ={"=[Time In]<= ([Time Out]+ time(24,'HH'))"}>}ID)

Re: Counting the number of people in the room

arixooo123 — Tue, 10 Apr 2018 03:02:30 GMT

You're right, id doesn't see to be correct

Isn't there any way to find all the hours that fall between Time In and Time Out and then count the IDs for each hour?

Re: Counting the number of people in the room

Anil_Babu_Samineni — Tue, 10 Apr 2018 03:12:42 GMT

Perhaps this?

Sum({<[Date In]={"=[Date In]=[Date Out]"},[Time In] ={"=[Time In]<= [Time Out]"} >}ID)

Sum({<[Date In]-={"=[Date In]=[Date Out]"},[Time In] ={"=[Time In]<= ([Time Out]+ time(24,'HH'))"}>}ID)

Re: Counting the number of people in the room

arixooo123 — Tue, 10 Apr 2018 03:18:32 GMT

Sorry but it made no difference

Re: Counting the number of people in the room

Anil_Babu_Samineni — Tue, 10 Apr 2018 03:37:48 GMT

Will you able to provide sample application to investigate?

Re: Counting the number of people in the room

arixooo123 — Tue, 10 Apr 2018 03:40:57 GMT

Sure but it's a huge app, how can I make a sample out of it?

Re: Counting the number of people in the room

Anil_Babu_Samineni — Tue, 10 Apr 2018 03:44:54 GMT

Scrambling Data

Re: Counting the number of people in the room

arixooo123 — Tue, 10 Apr 2018 05:26:11 GMT

Here is a sample file:

Re: Counting the number of people in the room

MarcoWedel — Tue, 10 Apr 2018 20:59:14 GMT

Hi,

one solution might be:

BINARY [https://community.qlik.com/servlet/JiveServlet/download/1467825-321465/test.qvw];

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='#,##0.00 €;-#,##0.00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

DROP Table Calendar;

NoConcatenate

tabEDPresentations:
LOAD RecNo() as ID,
    DateIn,
    TimeIn,
    DateOut,
    TimeOut
Resident EDPresentations;

DROP Table EDPresentations;

tabHours:
LOAD ID,
    Timestamp#(Timestamp(DateIn+TimeIn+(IterNo()-1)/24,'YYYY-MM-DD hh'),'YYYY-MM-DD hh') as Hour
Resident tabEDPresentations
While Timestamp#(Timestamp(DateIn+TimeIn+(IterNo()-1)/24,'YYYY-MM-DD hh'),'YYYY-MM-DD hh') <= DateOut+TimeOut;

hope this helps

regards

Marco

Re: Counting the number of people in the room

MarcoWedel — Tue, 10 Apr 2018 21:01:00 GMT

Re: Counting the number of people in the room

arixooo123 — Wed, 11 Apr 2018 01:08:57 GMT

Thanks Marco

Really appreciate it