New to Qlik and need help on dates

[#1]

I have two tables that contain dates and I need to calculate the amount of hours between the start time and top time. I've tried numerous ways based on the discussions online such as

floor([DispatchTimeClock.DispStopTime])-floor([DispatchTimeClock.DispStartTime])

or

interval(([DispatchTimeClock.DispStopTime]-[DispatchTimeClock.DispStartTime])

None of which are giving me the correct answer.

[sunny_talwar]

When you say they don't give correct answer, do you mean they give a number but the number is not what you expect or is it just not showing anything?

Some links related to the topic

Why don’t my dates work?

QlikView Date fields

Get the Dates Right

[#3]

Its not showing anything

[Digvijay_Singh]

You may try first on two constant time values, if it works then high chances that fields are not in proper date/time format, the suggested links will help to correct them.

[sunny_talwar]

Then you need to fix your dates

Get the Dates Right

[qlikviewwizard]

Did you try this?

Copied from HIC Blog.

2. Linking integer dates with fractional dates

You have a date in two different tables, and you want to use this date as a key, but it doesn’t seem to work. Then you should suspect that you have true dates (integers) in one table and timestamps (fractional numbers) in the other, but the formatting of the dates hides this fact.

How do I know whether this is the case? That’s easy. Just format it as a timestamp and see what you get. (List box properties – Number – TimeStamp)

The question is now what your list box looks like. If you have timestamps where hours, minutes and seconds are all zero, then you are all set. But if you have numbers in these places, then you need to use the Floor() function in the script to get integer dates. See QlikView Date fields.

Why don’t my dates work?

[ramasaisaksoft]

Hi Dave,

Mostly DB team had specified in string Format

i hope your source tables haven't data in proper format.so try to convert that data in date format just like

Date(date#(Book_Date,'YYYYMMdd'), 'MM/DD/YYYY') as "Book Date"

or

Date(date#(Book_Date,'YYYYMMdd HH:MM:SS'), 'MM/DD/YYYY HH:MM:SS') as "Book DateTime"

after that u can use intervalmatch([DispatchTimeClock.DispStopTime]-[DispatchTimeClock.DispStartTime] )

else

change the date format in Number tab as per Arjun specified

If still you didn't get the result just post your sample data so that our folks will help to you.