Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

compare 2 dates in different format

Hi

I want to compare two dates, first in format like 201405 and other in 5/1/2014.

I am using it in straight table and if condition gets true, then data for that date should display.

How to do that?

Thanks

Saurabh

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

=count({<Date={'$(=date(date#([Year Month],'YYYYMM'),'MM/DD/YYYY'))'}>}Product)


Change 'MM/DD/YYYY' to format of field Date

View solution in original post

9 Replies
PrashantSangle


Hi,

First convert both date in one format then compare it

I prefer convert it into Number then compare

Try like

Num(date(date#(firstDateField,'YYYYMM')))

AND

Num(secondDateField)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
bill_markham
Champion III
Champion III

First convert them to QlikView dates:

     date ( date# ( '201405 ' , 'YYYYMM' ) , 'DD MMM YYYY' )

     date ( date# ( '5/1/2014 ' , 'D/MM/YYYY' ) , 'DD MMM YYYY' )

and then compare them.

its_anandrjs

Provide any sample file.

Regards

Anand

ashfaq_haseeb
Champion III
Champion III

Hi,

Can you provide same data to look over?

Regards

ASHFAQ

Not applicable
Author

Hi

=count({<Num(date(date#([Year Month],'YYYYMM')))=Num(Date)>}Product)

I tried something like this, but it's giving error.

anbu1984
Master III
Master III

=count({<Date={'$(=date(date#([Year Month],'YYYYMM'),'MM/DD/YYYY'))'}>}Product)


Change 'MM/DD/YYYY' to format of field Date

Not applicable
Author

Hi

=count({<date ( date# ( '201405 ' , 'YYYYMM' ) , 'DD MMM YYYY' )=date ( date# ( '5/1/2014 ' , 'D/MM/YYYY' ) , 'DD MMM YYYY' )>}product)

it's giving error.

PrashantSangle

Hi,

In set Analysis you cannot compare like this,

Try With IF statement

like

=count(if(Num(date(date#([Year Month],'YYYYMM')))=Num(Date),Product))

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
jagan
Partner
Partner

Hi,

Can you attach some sample data?

Try like this in script

TableName:

LOAD

*,

If(Date1Formatted = Date2Formatted, 1, 0) AS Flag;

LOAD

*,

Date(Date#(Date1, 'YYYYMM')) AS Date1Formatted,

Date(Date#(Date2, 'M/D/YYYY')) AS Date2Formatted

FROM DataSource;

Now just use

Count({<Flag={1}>} Product)

Hope this helps you.

Regards,

Jagan.