Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Date comparison in expression

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 ?

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

11 Replies
tresesco
MVP
MVP

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.

Anonymous
Not applicable
Author

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

thanks

Miguel_Angel_Baeyens

It's the format mask

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

tresesco
MVP
MVP

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

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

Anonymous
Not applicable
Author

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

Miguel_Angel_Baeyens

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.

Anonymous
Not applicable
Author

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.

Miguel_Angel_Baeyens

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.

Anonymous
Not applicable
Author

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