Solved: Re: Counting the number of people in the room - Qlik Community

arixooo123 · ‎2018-04-09

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

MarcoWedel · ‎2018-04-10

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

View solution in original post

Anil_Babu_Samineni · ‎2018-04-09

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

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

arixooo123 · ‎2018-04-09

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 )?

Anil_Babu_Samineni · ‎2018-04-09

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))}

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

arixooo123 · ‎2018-04-09

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)

arixooo123 · ‎2018-04-09

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)

Anil_Babu_Samineni · ‎2018-04-09

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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

arixooo123 · ‎2018-04-09

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?

Anil_Babu_Samineni · ‎2018-04-09

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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

arixooo123 · ‎2018-04-09

Sorry but it made no difference