Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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.

Tags (2)
1 Solution

Accepted Solutions

Re: Re: Using Dates in an expression

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

6 Replies
oscar_ortiz
Valued Contributor

Re: Using Dates in an expression

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

MVP
MVP

Re: Using Dates in an expression

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;

MVP
MVP

Re: Using Dates in an expression

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)

Re: Re: Using Dates in an expression

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

Re: Using Dates in an expression

Thanks for the well formatted response.

Re: Using Dates in an expression

You're welcome

regards

Marco