Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Classifying

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

7 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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


talk is cheap, supply exceeds demand
Not applicable
Author

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


talk is cheap, supply exceeds demand