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

16 Replies
Highlighted

Will you able to provide sample application to investigate?

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

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

Highlighted

Scrambling Data

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

Here is a sample file:

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

Highlighted

Highlighted
Creator III
Creator III

Thanks Marco

Really appreciate it