Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate a Period with 2 Fields ?

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

1 Solution

Accepted Solutions
qlikmsg4u
Specialist
Specialist

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

View solution in original post

16 Replies
sunny_talwar

So are you trying to find difference between Arrival Date & Time and Shipment Date & Time. If yes, then may be this:

Interval(TimeStamp#([Arrival Date] & ' ' & [Arrival Time], 'MM/DD/YYYY hh:mm') - TimeStamp#([Shipment Date] & ' ' & [Shipment Time], 'MM/DD/YYYY hh:mm'), 'D hh:mm') as Difference

Not applicable
Author

Hi,

I guess you could try to create a Timestamp in the script with date time (MM/DD/YYYY HH:MM) and then calculate the direct diference between the two Timestamps. Timestamp1 - Timestamp2.

Because Timestamps are very memory consuming, I would recommend that you do this in a auxiliary table and then join the results into your original table, and in the end drop the auxiliary table.

Hope It was useful...

Best Regards,

Bruno Silva.

Ps- If you need just to count the working hours, it is a little more tricky than this...

maxgro
MVP
MVP

Interval(

     (Date#( [Arrival Date], 'MM/DD/YYYY') + Time#([Arrival Time], 'hh:mm'))

     -

     (Date#( [Shipment Date], 'MM/DD/YYYY') + Time#([Shipment Time], 'hh:mm'))

     )

sasiparupudi1
Master III
Master III

Please post some sample data

Not applicable
Author

Hello Everyone,

first of all I'd like to thank all the persons that have replied to my question.
I have tried the interval formula that you people provided but the metrics then comes into a weird format (A single number).
What I am looking for is that after measuring the difference between both shipment and arrival, it gives me the result in hours (if it is possible ?).
Again I thank you all for the answers you took the time to write in order to help me. I will recommend this community to anyone

Have a nice day all of you !

Not applicable
Author

I wanted to join my Excel table but I havent managed to do that..
Would you know how to do it ?
Cheers And thank you for answering

jonathandienst
Partner - Champion III
Partner - Champion III

Have a look at this blog posting for ways of handling and analysing multiple date fields:

Canonical Date

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sasiparupudi1
Master III
Master III

Untitled.jpg

Untitled2.jpg

sasiparupudi1
Master III
Master III

Please post your excel file