Discussion board where members can get started with Qlik Sense.
I made a dashboard to check the timing of my deliveries, but I'm in trouble because of the format of my input data.
I have a table like this:
SHIP. ID STATUS DATE
001 PICKED UP 2019-04-01
001 DELIVERED 2019-04-05
002 PICKED UP 2019-04-03
To check the average number of days for each delivery, I need to make the difference between DELIVERED and PICKED UP day for each shipment.
I tried to sum delivered date, then picked up date and make the difference, but I coould have some shipments not dilvered yet so the total could be wrong.
I'd like to add one more column after DATE one to put, if it's available, the delivery date of the shipment (in this case I'd like to add one more column to the first row with 2019-04-05 value).
I'm thinking about something similar to vlookup function..
Thank you very much for your help!
Go to Solution.
and what about if that value isn't in the line above? I mean if for example between the start and the end of the delivery I have one more line that could be for example 'Delivery Planned'.
in Qlik you can use previous to look to the data line above.
Check if the id is the same, then calc diffenrence.
.... your datas, order by id, date
if(SHIP.ID = previous(SHIP.ID), DATE-previous(DATE),null()) as delivering_time