topic Re: Date Format not working in QlikView

Date Format not working

anthony_kinsell — Wed, 07 May 2014 16:48:51 GMT

Hi,

I'm importing some data from an excel sheet & creating a temp file. I'm then creating a 2nd temp file from the original temp file & trying to format the date as MMM-YYYY however I'm not getting anything in this field - can anyone tell me what's wrong with my script? It's the bit in bold & red below:

I'm thinking script is correct but for some reason Qlikview is not recognising the data as a date from the excel sheet?

CPI_Temp:

CrossTable(CPIDate, Data)

LOAD

FROM

(biff, embedded labels, table is [average monthly rents indexed$], filters(

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 7)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2))

));

CPI_Temp2:

LOAD

CPIDate,

Date(CPIDate, 'MMM-YYYY') as YearMonth,

Data

Resident CPI_Temp;

DROP Table CPI_Temp;

Sample of the results below:

CPIDate	YearMonth	Data
		-
28/02/2007	-	100.91
28/02/2009	-	103.89
28/02/2010	-	100.61
28/02/2011	-	102.74
28/02/2013	-	106.08
28/02/2014	-	106.08
29/02/2008	-	105.66
29/02/2012	-	104.93

Re: Date Format not working

PrashantSangle — Wed, 07 May 2014 16:53:20 GMT

Hi,

Use MonthName(CPIDate) as MonthYear

Go with Date#()

Date(Date#(CPIDate,'DD/MM/YYYY'),'MMM-YYYY') as YearMonth

MonthName(Date#(CPIDate,'DD/MM/YYYY')) as YearMonth

Re: Date Format not working

anthony_kinsell — Wed, 07 May 2014 17:09:32 GMT

Thanks Max,

Much appreciated. This one worked:

Date(Date#(CPIDate,'DD/MM/YYYY'),'MMM-YYYY') as YearMonth

Why as a matter of interest do you have to use both Date & Date# - apologies if that is a silly question - relatively new to this type of thing.

Regards

Anthony