Qlik Community

Qlik GeoAnalytics Discussions

Discussion Board for collaboration regarding Qlik GeoAnalytics.

sarkar_mahendra
New Contributor

Calculate the time in hours between 2 dates

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

2 Replies
MVP
MVP

Re: Calculate the time in hours between 2 dates

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

MVP
MVP

Re: Calculate the time in hours between 2 dates

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

Community Browser