Classifying

[#1]

Dear all,

I have a list of visits with arrival datetime and departure datetime. I want to count the number of people on site each hour that is from 00:00 to 01:00, 01:00 to 02:00 etc. the issue is someone can visit at 01:30 and leave at 03:15 this means the person should be counted between 01:00 to 02:00 and again between 02:00 to 03:00 and 03:00 to 04:00.

my dimension is calculated dimension  as

=TIME(Class(ARR_TIME, 1/24),'hh:mm') & ' - ' &  timestamp(class(frac(ARR_TIME)+60/(24*60),60/(24*60)),'h:mm tt')

how best can I achieve this??

Thanks

[kaushiknsolanki]

Hi,

You need the Intervalmatch() function.

Here are some good links.

https://community.qlik.com/thread/93656

IntervalMatch

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/IntervalMatch_(Extended_Syntax)....

https://www.tutorialspoint.com/qlikview/qlikview_intervalmatch.htm

Regards,

Kaushik Solanki

[Gysbert_Wassenaar]

Then you'll have to split that record up into multiple records, one for each interval. That should be done in the script.

See for example Working between times

[#4]

correct me if I am wrong intervals work here to show how many people came in at a particular time band, but I want to count a person who came in before 7am and is still in at 3pm  as in at 10am to 11 am and 11 am to 12 pm

[#5]

correct me if I am wrong intervals work here to show how many people came in at a particular time band, but I want to count a person who came in before 7am and is still in at 3pm  as in at 10am to 11 am and 11 am to 12 pm

[Gysbert_Wassenaar]

Which is why you need to split up the record with one period into records of an hour each

[#7]

I think that's where I am getting lost, I know how to use interval but splitting im not sure what you mean sorry for being slow

[Gysbert_Wassenaar]

Read this discussion and the blog post it links to Working between times