Hi all,
I'm new to the community and this is my first post. I'm not new to Qlik Sense though and I've exhaustively looked for a solution to this but didn't come up with it myself or in any forum yet.
So I'm creating an app that among other functions, tells me what was the transit time of a shipment (by truck) was by subtracting two timestamps (Delivery - Departure); and it does this for many many many shipments for a network with many origins and destinations, so there are over 500 different lanes.
I have the transactional information in a DB2 database and I have the "logistics plan" information in a PostrgeSQL Datalake, for which I have different queries pulling complementary information and joining them in the Qlik Sense load script. On one hand I have the "Agreed/planned/ideal" transit time, loads per day and working days for each lane and on the other hand I have the timestamps of what actually happened out there.
What I want to do is compare the planned vs actual transit time for each move, which is very very simple except for one detail: The suppliers and truckload companies have different working days and this is considered in the planning. (e.g. A lane that ships on Monday with 5 working days/week and 5 days transit time should arrive next Monday. If the same route had 4 days transit time, it should arrive on Friday).
Another example to show the complexity: If a shipment departed on Friday 2nd at 8pm and had 6d 12h transit with 5 working days, it is supposed to arrive on Tuesday 13th at 8am, which is really 10d 12h later.
From my query, I can pull the number of working days per lane in a field (can be 5, 6 or 7), the problem is that I have lanes varying from 1 hour to 1 month in transit time, for which sometimes there are 5 weekends that I shouldn't count in the calculation. Likewise, for lanes that operate 6 days a week, there's only one day per week that I shouldn't be counting.
I really have no idea whatsoever of how I can do this using some dates math and even though I've done quite a bit of research I'm not familiar with all of the available functions.
I'm attaching a small extract (with the relevant fields) of the resulting table from the whole Qlik script. It's color coded based on the source of the information. The key is Origin-Destination and the column in yellow is what I'm trying to achieve. I'd attach the script here but my workplace is very sensitive about privacy.
