Date Format Calendar issue with Qlikview 10 not apparent in Qlikview 9

[#1]

We have an issue with a custom generated Calendar that now in Qlikview 10 is not pulling through the dates in the correct format.

I know there is a supposed fix for this in qlikview 10 sr1 but we are not in a position to upgrade at the moment so I was wondering if anyone could help re word our script to give us the date format we require.

In Qlikview 9 when pulling through a "work_day" field we would get the date in the following format

2007-12-01 00:00:00

2007-12-02 00:00:00

2007-12-03 00:00:00

This format works perfect for us and is used to base calculations on for graphs etc.

Once upgraded to Qlikview 10 all our Graphs started to display the error "no data to display" even though the script had not been changed.

Digging further I found when pulling through the same "work_day" field in Qlikview 10 now gave me the date format as below

40379.000000

40378.000000

40234.000000

Which obviously is not right and is what was causing the issues with our graphs.

I have since re-written the script a little, so intsead of it just selecting the raw field, the select statement now says:

to_char(work_day,'yyyy-mm-dd' hh:mm:ss) Work_Day,

I reloaded the script thinking this would work and it has to a point, now the date field format is as below

2007-12-01 12:01:00

2007-12-01 12:02:00

2007-12-02 12:03:00

For some reason Qlikview 10 is now creating hours and minutes which should not be there, and were not present in QV9. This therefore is still causing the graphs data not to be displayed and all calculations on the date fields are not working at all.

Is there anyway (without upgrading for now) to change my script to show the date format as above but as it was in QV9 with the zerored out hours, minutes and seconds?. Ideally I want to rewrite my line of code to give me the date field in this format again.

2007-12-01 00:00:00

2007-12-02 00:00:00

2007-12-03 00:00:00

If anyone could help it would be much appreciated. I have seen other posts mentioned the 'floor' function but with my lack of coding knowledge I am not sure how to implement this correctly into my existing code.

thanks

Lee Mackreath

[#2]

Sorry, not sure what you mean by changing the qlikview date formatting for it?

[#3]

Sorry, that's me confusing matters.

Add this in the preceding load:

timestamp( WORK_DAY_TEMP,'YYYY-MM-DD hh:mm:ss' )

This should format your date to have the 00:00:00

[#4]

Nope...if I add the timstamp I still get no hours minutes or seconds showing

[#5]

Go to Document Properties for your application and to the Numbers tab.
If you there mark your date field (WORK_DAY) you can then set the formatting from here.

Make sure the timestamp radiobutton is marked and that the format pattern is:

YYYY-MM-DD hh:mm:ss[.fff]

[#6]

Hi lmackreath78,

Please use the bellow mentioned code and changed your desired field. I hope this will work for you.

Thanks Regards,

==============================================================================

Load all these code in the individual tabs.

Calendar:

LOAD

D AS OrderDate,

D AS Date,

// Basic Date Dimensions

Y AS Year,

M AS Month,

MY AS MonthYear,

num(MY) AS NumericMonthYear,

'Q' & ceil(M / 3) AS Quarter,

week(D) AS Week,

weekday(D + 2) AS Weekday,

WY AS WeekYear,

day(D) AS Day,

date(D, 'MM/DD') AS DateMMDD

RESIDENT

DateIsland;

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

Caltemp:

LOAD

num(min(OrderDate)) AS MinDate,

num(max(OrderDate)) AS MaxDate

RESIDENT

Orders;

LET vMinDate = peek('MinDate', 0, 'Caltemp');

LET vMaxDate = peek('MaxDate', 0, 'Caltemp');

DROP TABLE Caltemp;

DateIsland:

LOAD

date($(vMinDate) + rowno() - 1) AS D,

year($(vMinDate) + rowno() - 1) AS Y,

month($(vMinDate) + rowno() - 1) AS M,

date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY,

week($(vMinDate) + rowno() - 1) & '-' & year($(vMinDate) + rowno() - 1) AS WY

AUTOGENERATE

vMaxDate - vMinDate + 1;

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

LET vToday = vMaxDate;

LET vMinToday = vMinDate;

LET vThisYear = year($(vToday));

LET vLastYear = year($(vToday)) - 1;

LET vThisQuarter = 'Q' & ceil(month($(vToday) / 3));

LET vThisMonth = num(month($(vToday)));

LET vThisMonthText = month($(vToday));

LET vLastMonth = num(month(addmonths($(vToday), -1)));

LET vLastMonthText = month(addmonths($(vToday), -1));

LET vTodayWithinYear = num($(vToday) - yearstart($(vToday))) + 1;

LET vTodayWithinMonth = num($(vToday) - monthstart($(vToday))) + 1;

[#7]

None of the suggestions are working!

I cant understand it!

it is clear to me that QV10 SR1 is messing with the date format still on all my apps. because of this one "work_day" field all my apps are failing to display graphs.

in QV9 the format came through as 2007-12-03 00:00:00

and now in qv10sr1 its coming through as 60379.000000

simple as that...no change in script...so how can I fix this so I get the orginal date format back?

[#8]

I just updated to V10 SR4 and having a similar problem:

DATE(datefield) AS DenomValue during the load is resulting in a value in julian format, instead of MM/DD/YYYY like it had in v8.5.  I have tried change the date in a multitude of ways, and no matter what, that field remains as a straight numeric.  Yet in v8.5 it displayed correctly.

I could force the charts that use that field to put it in a date format, except that the field contains a variety of values, and I want to fix the display.  Why doesn't the same command result in the same value??

[Miguel_Angel_Baeyens]

Hi,

Just for testing try

Date(datefield, 'MM/DD/YYYY') AS DenomValue

And make sure that in the first tab of the script, at the top, you have the following variables:

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff]';

And that those formats are the same that in your regional settings.

Take a look at the output of "DateFmt" in the Help menu, Document Support Info... Both Doc and Sys values should be the same.

Hope that helps.

Miguel

[#10]

I have those set - it's the first thing my load script sets.  It seems to be ignoring them.

I tried Date(datefield, 'MM/DD/YYYY') AS DenomValue, and it igores it.  I finally did

TEXT(Date(datefield, 'MM/DD/YYYY')) AS DenomValue

and it displays correctly, but now I fear it's no longer a dual?  I need to make sure I'm not breaking everything else by forcing it to be a text value.

I'm guessing I should report this to QV support.  This can't be right.

[Miguel_Angel_Baeyens]

Hi,

Certainly, Text() will format it as a literal, not as a dual value so some expressions might go wrong. What happens when you create a text object and set something like

=Date('10/01/2012')

Does it return a number or does it show properly?

Miguel