Mental block with formatting now() in Qlikview

[timsaddler]

Hi all,

I'm trying calculate how long it has been in hours since a ticket was delivered.

I have generated a date/time form the data as ESDate_Time in the screenshot.

I have generated the current datetime using the now() function.

Then using the interval function for the Elapsed Time which returns blank.

Interval(reporttime-ESDate_Time,'D hh:mm')

Can anyone point me in the right direction?

Thanks in advance

[vishsaggi]

Try this for your Current TIme:

Timestamp(Now(), 'DD/MM/YYYY hh:mm')

[timsaddler]

Thanks Vishwarath Nagaraju .

My interval function is still blank though?

[dplr-rn]

did you try Timestamp#() around both time values. just to eliminate any type issues

[marcus_sommer]

Your ESDate_Time isn't a timestamp else a string and you need to convert it to a timestamp maybe with something like:

Timestamp(timestamp#(ESDate_Time, 'DD/MM/YYYY hh:mm'))

- Marcus

[olivierrobin]

hello

try something like

let b=timestamp(timestamp#('21/09/2018 08:00','DD/MM/YYYY hh:mm'));

let a=timestamp(timestamp#('26/09/2018 15:35:29','DD/MM/YYYY hh:mm:ss'));

let c=timestamp#(timestamp(interval(a-b)),'D hh:mm');

[vishsaggi]

Try this?

= Interval(Timestamp(Now(), 'DD/MM/YYYY hh:mm') - TimeStamp(Timestamp#(ESDateTime, 'DD/MM/YYYY hh:mm')), 'D hh:mm')

[Miguel_Angel_Baeyens]

EDIT: Or, yeah, the Interval() function with the mask, indeed. Old customs...

The mask 'D hh:mm' is formatting a calendar date, not an interval, and the difference between two dates is always in days, for example 2,43 is 2 days, 10 hours and 19 minutes.

However, applying any Timestamp() or Date() function in QlikView will return something "strange" as day 1 in QlikView (the first possible date) is December 31, 1899.

Do Instead something like

Floor(date1-date2) & ' ' & Time(Frac(date1-date2), 'hh:mm')

to get a more sensible format, where Floor() returns the "integer" part in days and the decimal part, to the detail you need (hours, minutes, seconds, etc.), formatted using Time() as it cannot be longer than 1.

This could give you some ideas: Time Difference or Time in Human Readable Format

[timsaddler]

Thanks guys for all your helpful answers - i'm making some progress now but still some way to go - (had to do some other developments)