topic Dates in QlikView in QlikView

Dates in QlikView

Thu, 10 Mar 2011 18:01:43 GMT

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

Dates in QlikView

Thu, 10 Mar 2011 18:08:31 GMT

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;

Dates in QlikView

Thu, 10 Mar 2011 18:30:19 GMT

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,

Dates in QlikView

Miguel_Angel_Baeyens — Thu, 10 Mar 2011 20:04:05 GMT

Hello,

This should do:

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

Hope that helps

Dates in QlikView

Fri, 11 Mar 2011 11:44:28 GMT

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