topic Re: Using Dates in an expression in QlikView

Using Dates in an expression

Wed, 12 Nov 2014 22:21:08 GMT

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.

Re: Using Dates in an expression

oscar_ortiz — Wed, 12 Nov 2014 22:38:36 GMT

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

Re: Using Dates in an expression

maxgro — Wed, 12 Nov 2014 22:41:00 GMT

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;

Re: Using Dates in an expression

MK_QSL — Wed, 12 Nov 2014 22:41:03 GMT

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

MarcoWedel — Wed, 12 Nov 2014 23:19:44 GMT

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

Re: Using Dates in an expression

Thu, 13 Nov 2014 21:26:21 GMT

Thanks for the well formatted response.

Re: Using Dates in an expression

MarcoWedel — Thu, 13 Nov 2014 21:44:29 GMT

You're welcome

regards

Marco