40 Replies Latest reply: Jan 10, 2012 11:44 AM by Sally Hurley RSS

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

      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