topic Re: Comparing Dates in QlikView

Comparing Dates

Anonymous — Thu, 26 Jul 2012 05:47:35 GMT

Hello,

I am pretty new to qlikview and I am facing a problem with comparing of DateTime values in a dynamic dimension of an Pivot Diagram,

using the following formula:

=if([ERG-DateTime]>=var_datetime, timestamp([ERG-DateTime]) & ' >=' & var_datetime ,timestamp([ERG-DateTime]) & '<' & var_datetime)

var_datetime is a datetime value formated DD.TT.YYYY hh:mm from an inputbox

ERG-DateTime is a Field with datetime values formated accordingly.

Unfortunately is the comparison not working correctly. Towards the end of the each month the if clause is interpreted wrong delivering e.g.:

31.01.2012 17:14:00 >= 26.05.2012 23:59

Thus the if clause is not interpreted correctly. Can someone help solving this issue?

Thanks for your assistance.

Schmat

Anonymous — Thu, 26 Jul 2012 06:00:40 GMT

Not quite sure what you mean but the formatting looks wrong:

DD.TT.YYYY hh:mm

TT should be MM no?

Anonymous — Thu, 26 Jul 2012 06:15:54 GMT

Yes, sorry that was my typo.

Formating ist DD.MM.YYYY

Let me elaborate a bit on what I mean:

Currently the Formula returns the values of the Field and the variable as text. (which I did for testing):

[ERG-DateTime]=31.01.2012 17:14:00

var_datetime=26.05.2012 23:59

Nevertheless the if clause returns "true" giving as a result:

31.01.2012 17:14:00 >= 26.05.2012 23:59

This result is obviously wrong, but I dont know why this happens.

Regards,

Schmat

Anonymous — Thu, 26 Jul 2012 06:33:18 GMT

Ok, sounds like your variable, and/or field value is/are not being recognized as date values.

You can use:

timestamp#(var_datetime,'DD.MM.YYYY hh:mm')

instead of just var_datetime in your expression to set the interpretation format.

Use the same for [ERG-DateTime] and you should be good to go

Anonymous — Thu, 26 Jul 2012 07:23:35 GMT

This formating solved the Problem.

It was caused by the facht, that the field values showed including seconds while the variable date was only showing minutes...

Thx for the assistance.