topic Re: Date comparison issue in QlikView

Date comparison issue

Mon, 01 Jun 2015 14:24:00 GMT

Hi All,

I am facing a strange issue.

When I use the below expression for comparing the date format, it returns -1 even though the date format doesn't match.

IsNum((Date#('Mar 2015','MMMM YYYY'))) ,

When I use the below expression, it returns 0 as expected.

=IsNum((Date#('201501','YYYY-MM')))

It is just that this expression is not able to compare date in MMM and MMMM Format.

Is there anything wrong with the expression i am using?

Can we use a generic expression for comparing all the date formats?

Thanks,

Asma

Mon, 01 Jun 2015 14:27:37 GMT

Try this...

=IsNum((Date#('Mar 2015','MON YYYY')))

vvvvvvizard — Mon, 01 Jun 2015 14:30:59 GMT

try IsNum((Date#('Mar 2015','MMM YYYY'))) with 3 M instead of 4

jonathandienst — Mon, 01 Jun 2015 14:31:14 GMT

Qlikview seems to treat MMM and MMMM interchangeably:

Date#('Mar 2015','MMMM YYYY')

Date#('March 2015','MMMM YYYY')

Date#('Mar 2015','MMM YYYY')

Date#('Mar 2015','MMMM YYYY')

all produce the same output. This returns null:

Date#('Mar 2015','MM YYYY')

Mon, 01 Jun 2015 14:31:46 GMT

Irrespective of using 3Ms or 4Ms, the expression is always returning -1

Mon, 01 Jun 2015 14:33:37 GMT

The expression =Date#('Mar 2015','MM YYYY') is returning Mar 2015

antoniotiman — Mon, 01 Jun 2015 14:51:31 GMT

Hi Asma,

Use Alt() function

From Guide :

Alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

Regards,

Antonio