Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using Dates in an expression

Hi,

I am trying to use a date formatted MM/DD/YYYY and compare it to today's date in an expression.  I would like to have all dates before the current date flagged and all the dates after the current date to remain unflagged.  The expression that is not working for me is as follows:

if( DateField < Today(), 1,0)

I'm just trying to flag all dates before today as 1, all dates after today as 0.

Any help would be greatly appreciated.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

I agree that

if( DateField < Today(), 1,0)

not working suggests that DateField is Text and not Dual.

So your approach

If(Date(Date#(DateField,'DD/MM/YYYY'))<Date(Today()),1,0)

should work fine.

I'd like to add that the outer date() functions in this case don't change the value and thus should not influence the comparison, i.e.

If(Date#(DateField,'DD/MM/YYYY')<Today(),1,0)

should work as well and can even be abbreviated to

-(Date#(DateField,'DD/MM/YYYY')<Today())

hope this helps

regards

Marco

View solution in original post

6 Replies
oscar_ortiz
Partner - Specialist
Partner - Specialist

I would simply wrap both sides of that expression with the Num() function to compare apples to apples.

If( Num(DateField) < Num(Today()), 1, 0) as DateFlag

Good luck

Oscar

maxgro
MVP
MVP

q:

load

  DateField,

  if(DateField < date(Today(), 'MM/DD/YYYY'),1,0) as Flag;

load

  date(floor(today() + rand()*100 - rand()*100), 'MM/DD/YYYY') as DateField

autogenerate 10000;

MK_QSL
MVP
MVP

Load

  *,

  If(Date(Date#(DateField,'DD/MM/YYYY'))<Date(Today()),1,0) as Flag

Inline

[

  DateField

  01/01/2014

  14/05/2014

  10/11/2014

  15/12/2014

  01/01/2015

];

Or Simply use below expression at UI end

If(Date(Date#(DateField,'DD/MM/YYYY'))<Date(Today()),1,0)

MarcoWedel

Hi,

I agree that

if( DateField < Today(), 1,0)

not working suggests that DateField is Text and not Dual.

So your approach

If(Date(Date#(DateField,'DD/MM/YYYY'))<Date(Today()),1,0)

should work fine.

I'd like to add that the outer date() functions in this case don't change the value and thus should not influence the comparison, i.e.

If(Date#(DateField,'DD/MM/YYYY')<Today(),1,0)

should work as well and can even be abbreviated to

-(Date#(DateField,'DD/MM/YYYY')<Today())

hope this helps

regards

Marco

Not applicable
Author

Thanks for the well formatted response.

MarcoWedel

You're welcome

regards

Marco