topic Subtracting dates in QlikView

Subtracting dates

Wed, 25 Apr 2012 11:14:47 GMT

Hi,

In the attached QVW for the field "DeliveryDate" I want to subtract the minimum date from the maximum date for a given container number.

It doesn't work. I'm pretty sure it is because of the date format. Any suggestions what I'm doing wrong? Source html files attached as well.

BR. Jakob

christophebrault — Wed, 25 Apr 2012 11:54:40 GMT

hi,

Yes, your problem is because of date format.

See attachef file

if you have a PE edition :

Script :

Containers:

LOAD [Contnr ID],

SubField( Deliv.Date,'.',3)&'-'&SubField(Deliv.Date,'.',2)&'-'&SubField( Deliv.Date,'.',1) as DeliveryDate,

Containers,

left(SubField(FileName(),'-',3),4)&'-'&SubField(FileName(),'-',2)&'-'&right(SubField(FileName(),'-',1),2) as ExtractDate

FROM

// Change with your path

(html, codepage is 1252, embedded labels, table is @1);

expression in graph :

MinDate:

date(min(DeliveryDate))

MaxDate:

date(max(DeliveryDate))

Difference :

MaxDate-MinDate

Wed, 25 Apr 2012 12:35:04 GMT

Hi,

you just have to change your loading script as follows to match the string date format

LOAD [Contnr ID],

date(date#(Deliv.Date,'DD.MM.YYYY')) as DeliveryDate,

//Containers

Today() as ExtractDate

FROM

.....