topic Count the number of crossing dates in QlikView

Count the number of crossing dates

oxiofrt — Fri, 06 Mar 2015 11:42:49 GMT

Hi,

i have some events with a beginning and an ending date (with hours, minutes, ...)

I would like (without using sql loading or scripts) to calculate for each event, how many are in progress (counting itself).

For example with 3 events (event, date b

Event Date begin Date end Number of events in progress

A 11/02/2012 08:00:00 11/02/2012 08:30:00 1

B 11/02/2012 08:10:00 11/02/2012 08:50:00 2

C 11/02/2012 08:15:00 11/02/2012 08:55:00 3

D 11/02/2012 08:40:00 11/02/2012 10:00:00 3

D 11/02/2012 09:00:00 11/02/2012 10:30:00 2

D 11/02/2012 11:00:00 11/02/2012 12:00:00 1

I have tried several ways but not of them worked well...

You will find herejoined a test app

Thanks a lot for your help

Re: Count the number of crossing dates

Gysbert_Wassenaar — Fri, 06 Mar 2015 11:45:28 GMT

See this blog post: Creating Reference Dates for Intervals

Re: Count the number of crossing dates

oxiofrt — Fri, 06 Mar 2015 11:50:19 GMT

Thanks a lot for your answer

However, I would like to create a formula rather than using scripts.

Re: Count the number of crossing dates

Gysbert_Wassenaar — Fri, 06 Mar 2015 12:16:53 GMT

Good luck with that. You'll have to compare every row in your chart with every other row in your chart. That's a lot of if statements for one expression.