Time orders are live for

[sifatnabil]

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.

[rrsrini2907]

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.

[swuehl]

Maybe have a look at

Re: Calculate hours between two Date/Time strings

[sifatnabil]

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

[swuehl]

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

[#6]

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 ?

[sifatnabil]

Hi, please see attached.

[swuehl]

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

[sifatnabil]

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?

[swuehl · Accepted solution]

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.