How to calculate a Period with 2 Fields ?

[#1]

Hello everyone,

I have a table with 4 Columns :

- Shipment Date

- Shipment Time

- Arrival Date

- Arrival Time

Both of my Date columns contain data in such format : MM/DD/YYYY

As for my time Column, the data is represented as : HH:MM

What I am trying to do is to create a metric that would take into account both time and Date for Arrival and Shipment so that it gives back a Shipping Time Value for each entry of my table. If anyone can help I'd be really graceful.
Thanks a lot
Alexandre

[#2]

Here is my excel table

[qlikmsg4u · Accepted solution]

Hi Alex,

Try this

Test:

Load *,Round((Timestamp(Date(ArrivalDate)&' '&Time(ArrivalTime),'MM/DD/YYYY hh:mm') - Timestamp(Date(ShippedDate)&' '&Time(ShippedTime),'MM/DD/YYYY hh:mm'))*1440) as Duration;

LOAD [Created Date],

    [Created Time],

    [Issued Date],

    [Issued Time],

    [Shipped Date] as ShippedDate,

    [Shipped Time] as ShippedTime,

    Manifests,

    POU,

    [Req Qty],

    [Issue Qty],

    [Req Qty/Vendor],

    [Issue Qty/Vendor],

    Status,

    [Job Refs],

    Batches,

    [Arrival Date] as ArrivalDate,

    [Arrival Time] as ArrivalTime

FROM

Source

[sasiparupudi1]

Hi Alexandre,

please try below

Interval(timestamp#(Date([Arrival Date],'D/MM/YYYY')& ' ' & Time([Arrival Time],'H:MM'),'D/MM/YYYY HH:MM')-timestamp#(Date([Shipped Date],'D/MM/YYYY')& ' ' & Time([Shipped Time],'H:MM'),'D/MM/YYYY HH:MM'),'H')  as Interval,

Date([Shipped Date],'D/MM/YYYY')& ' ' & Time([Shipped Time],'H:MM') as [Shipped Date Time],

Date([Arrival Date],'D/MM/YYYY')& ' ' & Time([Arrival Time],'H:MM') as [Arrival Date Time];

HTH

Sasi

[sasiparupudi1]

Hi

Any luck?

Sasi

[#6]

Thanks a lot it gives me the number of minutes between the 2 dates and so I can convert that into hours

[#7]

Yes it worked with your solution too ! Thx a lot !

[qlikmsg4u]

Yes you are right, i calculated duration in minutes..Obviously you can convert these into hours.

If you are satisfied with the answers, please close the discussion so that others will find solutions/answers if they encounter the same situation.