Date not showing up..Please Help

[#1]

Hi All,

I have used the Date() function but for some reason the date isn't getting displayed. I am attaching the QVD and QVW as data sample.

Thanks in Advance,

[Peter_Cammaert · Accepted solution]

Date() will format values of regular date fields, but it will not convert what it cannot understand. [fkdimdate] contains large numerical values like 20081101 which it cannot understand. Today is day number 42432, so your large number 20081101 is still 54864 years in the future.

You should first use the Date#() function to tell QlikView what format the date value in [fkdimdate] is in.

For example, =Date#(fkdimdate, 'YYYYMMDD') will tell QlikView that those large numbers are in reality a date specification with a particular Year-Month-Day order. The return value of this Date#() call will be a proper QlikView date field.

Then you can format the date value into a MonthYear representation  (actually you should call that one "YearMonth" instead of MonthYear because of the order) by giving it a proper format using the Date() function. Your original code

... year(date(fkdimdate)) & '-' & num(month(date(fkdimdate))) ...

should be

... Date(Date#(fkdimdate, 'YYYYMMDD'), 'YYYY-MM') ...

or

... Date(Date#(fkdimdate, 'YYYYMMDD'), 'YYYY-M') ...

if you don't like leading zeroes in the month number.

Remember: Date() is used for formatting existing date values, Date#() is for interpreting and converting unrecognised numbers into real date values.

Best,

Peter

[#3]

Thanks Peter for your help.