Table column with lookup formula

[da_pagnott]

Hi guys,

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!

[martinpohl]

Hi,

in Qlik you can use previous to look to the data line above.

Check if the id is the same, then calc diffenrence.

load

.... your datas, order by id, date

then load

if(SHIP.ID = previous(SHIP.ID), DATE-previous(DATE),null()) as delivering_time

Regards

[da_pagnott · Accepted solution]

Hello,

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'.

Thank you,