Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Counting the number of people in the room

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

1 Solution

Accepted Solutions
Highlighted

Hi,

one solution might be:

QlikCommunity_Thread_297637_Pic1.JPG

QlikCommunity_Thread_297637_Pic2.JPG

QlikCommunity_Thread_297637_Pic3.JPG

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

16 Replies
Highlighted

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

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)

Highlighted
Creator III
Creator III

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)

Highlighted

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

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?

Highlighted

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator III
Creator III

Sorry but it made no difference