Dates in QlikView

[#1]

I have information being pulled from a view in SQL Server into a QV document.

One of the columns being pulled from the view is date_received and is formatted like so "28/02/2011 00:00:00"

In my script I have the dates formatted as such

---------------------------------------------------------------------------------------------------------------------------------

Date:
load
today()-recno()+1 as Date
autogenerate(today()-'2008-01-01');

DateParts:
load
Date as MyDate,
Year(Date) as Year,
yearname ( Date, 0, 4 ) as FinancialYear,
date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,
date(week(Date),'WW-YYYY') as Date_WeekYear,
Month(Date) as FiscalRollMth,
Month(Date) &'-'&right(Year(Date),2) as RollShortMth,
Ceil(Month(Date)/3) &'-'&right(Year(Date),2) as RollShortQrt,
Month(Date) as Month,
Day(Date) as Day,
Week(Date) as Week

resident Date;

drop table Date;

---------------------------------------------------------------------------------------------------------------------------------

My question is how do have the dates from 'date_received' as the main driver for my dates.

When i put 'Month' and 'Year' list boxes on my page it doesnt seem to pick up on these dates and there is no relationship whatsoever. When i link the tables and put 'date_received as MyDate' the format is changed from "28/02/2011 00:00:00" to a 5 digit string, for example, '40602'.

Apologies for posting such a long question, but hopefully i've been clear enough in trying to portray the issue. Any help or guidance would, as always, be greatly appreciated.

Cheers

[#2]

DateParts:
load

Date as 'date_received',

Year(Date) as Year,
yearname ( Date, 0, 4 ) as FinancialYear,
date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,
date(week(Date),'WW-YYYY') as Date_WeekYear,
Month(Date) as FiscalRollMth,
Month(Date) &'-'&right(Year(Date),2) as RollShortMth,
Ceil(Month(Date)/3) &'-'&right(Year(Date),2) as RollShortQrt,
Month(Date) as Month,
Day(Date) as Day,
Week(Date) as Week

resident Date;

[#3]

I guess that qlikview will not recognise the format on date_received? So this is my suggestion:

makedate(subfield(subfield(date_received,' ',1),'/',3),subfield(subfield(date_received,' ',1),'/',2),subfield(subfield(date_received,' ',1),'/',1)) as MyDate,

[Miguel_Angel_Baeyens]

Hello,

This should do:

Date:load Date(today()-recno()+1) as Dateautogenerate(today()-'2008-01-01');

Hope that helps

[#5 · Accepted solution]

Thanks for your help everyone. I finally found a resolution to my problem. Instead of having a seperate dates table I integrated the dates into the other tables. Its something which i should have done first and in doing so probably would have avoided all the confusion. Heres the code:

-----------------------------------------------------------------------------------

LOAD customer_no,
"date_received" as Date,
Year(date_received) as Year,
Month(date_received) as Month,
-----------------------------------------------------------------------------------

Anyway, thanks again for the help. Cheers