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
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
You consider Time Out is the variable? So, you can think about this?
Count( {< [Time In] = {"=[Time In]<= [Time Out] "} >} Id)
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 )?
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))}
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)
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)
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)
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?
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)
Sorry but it made no difference