topic Date comparison in expression in QlikView

Date comparison in expression

Anonymous — Fri, 28 Jul 2017 09:04:45 GMT

Hi all,

I want to do a daily analisys so in the load script I have

LET TodayDate = today();

In the expression I compare with the field :

dtimport_formated that is obtained from dtimport in mysql load by:

CAST(dtimport AS DATE) as dtimport_formated

dtimport is an integer in the format : 20170710

the expression I use is

Sum({<dtimport_formated={$(TodayDate)}>}valor_brt)

it returns nothing, but...

if I use

LET TodayDate = date(today(),'YYYYMMDD');

and then compare it with

Sum({<dtimport={$(TodayDate)}>}valor_brt)

it works fine, but the date instead of showing dd/MM/YYYY is presented in YYYYMMdd

can anyone help ?

Re: Date comparison in expression

tresB — Fri, 28 Jul 2017 09:20:44 GMT

Since they are different at places in format, once you have to use an extra formatting expression, either in set analysis or when you use the variable somewhere else. I.e. is if you get the variable with integer formatted, you can use date() while use variable somewhere like: =Date($(TodayDate), 'DD/MM/YYYY'); either way.

Re: Date comparison in expression

Anonymous — Fri, 28 Jul 2017 09:40:56 GMT

Hi, it almost works, but it converts 20170701 into 02/07/57125 instead of 01/07/2017

thanks

Re: Date comparison in expression

Miguel_Angel_Baeyens — Fri, 28 Jul 2017 09:43:09 GMT

It's the format mask

Date(Date#($(TodayDate), 'YYYYMMDD'))

Re: Date comparison in expression

tresB — Fri, 28 Jul 2017 09:49:37 GMT

If you are using on integer value variable, try like:

= Date(Date#($(TodayDate), 'YYYYMMDD'), 'DD/MM/YYYY')

Re: Date comparison in expression

Anonymous — Fri, 28 Jul 2017 10:37:51 GMT

Nope, I' tried almost everything (if I've tried everything it would be solved) and still nothing.

the dates appera correct but they do not compare right

Re: Date comparison in expression

Miguel_Angel_Baeyens — Fri, 28 Jul 2017 10:49:35 GMT

Then the format in the fields and variables are different. Once they are the same, it will be fixed, but without a sample QVW, there is little else to check exactly where this difference resides.

As a side note, in the set analysis you also need to format the output to match with the field value format, for example:

Sum({< dtimport = {"$(=Date($(TodayDate), 'DD/MM/YYYY'))"} >} valor_brt)

Or even easier, use the numeric value instead of the date format in all fields. Also better performing.

Re: Date comparison in expression

Anonymous — Fri, 28 Jul 2017 10:51:43 GMT

Im using the numeric one right now, and it will suffice, but it really annoys me not being able to do it the way I want.

I cannot send you the qvw because it contains company data.

Re: Date comparison in expression

Miguel_Angel_Baeyens — Fri, 28 Jul 2017 11:27:11 GMT

I figured as many of us handle sensitive data we cannot share, but without having a real app with real data, the troubleshooting is limited.

You could put side by side in a tablebox both fields, or in a text object the variable and on the other one value of the field, and see if, for example, one has time and the other only the date, how they display, etc.

Re: Date comparison in expression

Anonymous — Fri, 28 Jul 2017 12:57:01 GMT

the table was a good idea, found some stuff wrong, in the results, Date($(TodayDate)) and $(TodayDate) present 30/12/1899 but htey appear correct in the header

Re: Date comparison in expression

Miguel_Angel_Baeyens — Fri, 28 Jul 2017 14:23:22 GMT

That likely means you need to use Date('$(TodayDate)') or '$(TodayDate)' with single quotes

Re: Date comparison in expression

Anonymous — Fri, 28 Jul 2017 14:36:52 GMT

THANKS MIGUEL THATS IT !!!!