Comparing Dates

[Anonymous]

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]

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

DD.TT.YYYY hh:mm

TT should be MM no?

[Anonymous]

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 · Accepted solution]

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]

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.