topic Re: timestamps error in QlikView

timestamps error

0li5a3a — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

I have a filed in my DB which contain the Date with the stamp, If I put the colun in a chart or table I can see the timestamp but if I create a text box to display thre date is display only -.

I need to calculate the number of hours between the time now() and my date from my DB and display red if is more then 12h or green if is < 12h.

I have tried

=if(Num(Hour(interval(now()-Date)))>'12:00:00','Red','Green') but this is not right.

Regards,

Re: timestamps error

sunny_talwar — Thu, 15 Mar 2018 13:35:29 GMT

How about this

=If((Now() - Date) > MakeTime(12), 'Red', 'Green')

Re: timestamps error

Chanty4u — Thu, 15 Mar 2018 13:37:50 GMT

expression working in text box

=if(Num(Hour(interval(now()-Date)))>'12:00:00','Red','Green')

Re: timestamps error

sasiparupudi1 — Thu, 15 Mar 2018 13:47:10 GMT

what is your timestamp format

Re: timestamps error

Peter_Cammaert — Thu, 15 Mar 2018 13:54:59 GMT

A few preliminary remarks:

Are you sure Date contains a Datetime value that QlikView can understand? If the text string (that's what you get out of a DB most of the time) cannot be interpreted as a Datetime value, then Now()-Date will produce a NULL value and you will get a hyphen in your text box.
Are you sure that field Date at any moment contains just a single value? Fields can have multiple active values. If you then use that field in an expression, QlikView will implicitly apply an Only() call to the field and that call will return NULL if more than one value is active.

BTW the simplest expression is probably this (on condition that Date has but a single correct DateTime value):

=IF (LocalTime() - Date > 0.5, 'Red', 'Green')