topic Re: Time orders are live for in QlikView

Time orders are live for

sifatnabil — Fri, 06 May 2016 10:57:44 GMT

Hi all,

I would like to know within market hours (7am to 7pm) how many hours a client has an order live on screen. For example, client A put in these orders which from were on screen from the entry and exit times:

Order, Entry, Exit

Order 1, 8am, 3pm

Order 2, 5pm, 6pm

Order 3, 8am, 2pm

In this scenario, client A had orders live for 8 hours: 8 - 3pm, 5 - 6pm = 1 hour, NOT 14 hours (which is what you would get if you included Order 3: 8 - 2pm).

How could I derive this time on screen in script or expression? FYI my entry and exit formats are datetime.

Re: Time orders are live for

rrsrini2907 — Fri, 06 May 2016 11:03:04 GMT

Hi,

As per my understanding order 3 is the overlap of order 1 and hence you will not add into your report.

So for the day, you would consider only order 1 and order 2.

Is that correct?

Regards,

Srini.

Re: Time orders are live for

swuehl — Fri, 06 May 2016 11:03:31 GMT

Maybe have a look at

Re: Calculate hours between two Date/Time strings

Re: Time orders are live for

sifatnabil — Fri, 06 May 2016 11:05:39 GMT

That's correct. Order 3 is an overlap and hence must be excluded.

Re: Time orders are live for

swuehl — Fri, 06 May 2016 11:07:49 GMT

You can create subintervals from your intervals, like described in HIC's tech doc, sub chapter Interval partitioning (pretty much at the end of the doc):

IntervalMatch and Slowly Changing Dimensions

Re: Time orders are live for

Fri, 06 May 2016 11:10:39 GMT

You can use Interval Match and add the flag to start and end if you have any overlap. I believe you have more that 3 fields.

Can you please share the sample data ?

Re: Time orders are live for

sifatnabil — Fri, 06 May 2016 11:17:44 GMT

Hi, please see attached.

Re: Time orders are live for

swuehl — Fri, 06 May 2016 11:41:55 GMT

Maybe something along attached sample (following the tech doc I've referenced in my previous post).

Re: Time orders are live for

sifatnabil — Mon, 09 May 2016 15:59:10 GMT

Hi swuehl‌, this almost works - however, Interval(Sum(SubEnd-SubStart)) is including intervals between Order exit and Order entry; however, I would like to only include intervals between Order entry and Order exit. Is there a way to exclude these intervals?

Re: Time orders are live for

swuehl — Mon, 09 May 2016 21:23:12 GMT

Yes, I should have just follow Henric tech's doc. I even forgot the final Intervalmatch.

Please check if attached is closer to what you are expecting.