Calculate the time in hours between 2 dates

[Anonymous]

Hi

I am new to QlikView. I am preparing a report where I need to calculate the time between two dates to be converted in hours.

How do I go about writing the script.

Regards

Mahendra

[swuehl]

Dates / timestamps in Qlik are stored as dual values, i.e. they have an underlying numeric values (integers denoting days since Dec 30th, 1899, fraction of decimals denoting time of the day, i.e. 0.25 represents 6 AM) and a text representation (showing the date / timestamp in the format you want).

So it's important to ensure your dates / timestamps are read in correctly when you load your data:

Get the Dates Right

Why don’t my dates work?

Then it's easy to calculate the difference between two timestamps, you can just subtract the values:

LOAD

     EndTimestampField - StartTimestampField as Duration

     EndTimestampField,

     StartTimestampField,

     ...

FROM ..;

This will show a number, which you then can format as you like using Interval() function:

Interval( EndTimestampField - StartTimestampField, 'hh:mm:ss') as Duration

[swuehl]

P.S. The Duration values are like dates showing hours as fractions of days (0.25 represents 6 hours, 1.25 represents 30 hours. If you need your values to represent hours in integer values, you can multiply by 24 and round your values).

Round( ( EndTimestampField - StartTimestampField)*24) as DurationInHours