Set Analysis problems with dates

RedSky001 — Tue, 08 Nov 2011 11:02:43 GMT

Dimension_tmp:LOAD  * INLINE [
"AdID","Ad Local End Time"
"230112091","2010-11-12T23:59:00"
"230112092","2011-11-12T23:59:00"
];

Dimension:
load  *,
Date(SubField([Ad Local End Time],'T',1),'DD/MM/YYYY')                         AS     [Ad Local End Date]
RESIDENT Dimension_tmp;

DROP  TABLE Dimension_tmp;


Fact:
LOAD  * INLINE [
    Date, AdID,Impressions
    01/11/2011,230112091,1
    10/11/2011,230112091,1
    11/11/2011,230112092,1    
];

See the attached QVQ.

I'm trying to exlude AdId 230112092 (as it's expired)

However with this expression it still shows.

sum({$<[Ad Local End Date]-={"<$(=vMinDate)"}>} Impressions)

If I hard code the min date value it works, so I'm guessing it's something to do with date formatting. Please help.

sum({$<[Ad Local End Date]-={"<01/11/2011"}>} Impressions)

Regards

Message was edited by: msheraton I decided the convert the date to a number then compare. So in the script: floor(Date(SubField([Ad Local End Time],'T',1),'DD/MM/YYYY')) AS [Ad Local End Date Floor] and my expression: sum({$<[Ad Local End Date Floor]-={'<$(=min(Total Date))'}>} Impressions) Seems to work. Mark

Set Analysis problems with dates

Anonymous — Tue, 08 Nov 2011 11:22:45 GMT

Hi msheraton,

(I do not download files, so may be mistaken.)
It looks like your Date field is not in a date fromat. Try this:

Dimension:
load *,
Date(date#(SubField([Ad Local End Time],'T',1),'YYYY-MM-DD'),'DD/MM/YYYY') AS [Ad Local End Date]
RESIDENT Dimension_tmp;

topic Set Analysis problems with dates in QlikView

Set Analysis problems with dates

Set Analysis problems with dates