Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
da_pagnott
New Contributor III

Table column with lookup formula

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!

Labels (5)
1 Solution

Accepted Solutions
da_pagnott
New Contributor III

Re: Table column with lookup formula

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,

2 Replies
martinpohl
Valued Contributor II

Re: Table column with lookup formula

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
New Contributor III

Re: Table column with lookup formula

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,