Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Classifying

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
MVP & Luminary
MVP & Luminary

Re: Classifying

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
Highlighted
Not applicable

Re: Classifying

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

Highlighted
Not applicable

Re: Classifying

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Classifying

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
Highlighted
Not applicable

Re: Classifying

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Classifying

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


talk is cheap, supply exceeds demand