Counting the number of people in the room

[arixooo123]

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

[Anil_Babu_Samineni]

Will you able to provide sample application to investigate?

[arixooo123]

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

[Anil_Babu_Samineni]

Scrambling Data

[arixooo123]

Here is a sample file:

[MarcoWedel · Accepted solution]

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

[MarcoWedel]

see also:

Creating Reference Dates for Intervals

[arixooo123]

Thanks Marco

Really appreciate it