Date sum

[gabrielperin]

Hello Qlik Sense community

I need to know how long a vehicle has been down for maintenance based on service orders.

The information appears as follows:

SO | Start | End

2554 | 01/02/2022 | 03/02/2022

2555 | 02/02/2022 | 05/02/2022

2556 | 08/02/2022 | 02/15/2022

...

The problem is: if I do (End - Start) and add the SO, I will have overlapping sums, because it can happen that a service order is closed without the previous one being closed.

How could I do a sum without a data overlay?

[rubenmarin · Accepted solution]

Hi, you can take help from script, there you can do a sorted (order by) load, and use Peek() or Previous() to access the value of the previous record to check if the start date is lower than the previous end date, something like:

ProcessedTable:
LOAD
  *,
  AccumEnd-If(Start<Peek(AccumEnd), Peek(AccumEnd), Start) as AccumDuration
;
LOAD
  VehicleID,
  SO,
  Start,
  End,
  If(End<Peek(AccumEnd), Peek(AccumEnd), End) as AccumEnd, // In case the previous end was higher
  End-Start as Duration
Resident DataTable
Order By
VehicleID, Start;

DROP Table DataTable;

Then a Sum(AccumDuration) should not count ovelapped days.

Maybe it needs some fix.

[gabrielperin]

Worked perfectly!

Thanks for the  contribution!!