5 Replies Latest reply: Nov 17, 2011 3:53 PM by John Witherspoon RSS

    Dates for Graphs

      Hi,

       

      I want to create a chart with the date on the X-axis. The date should be displayed per month. Not that the month will go from one year to another, thus Nov, Dec, Jan, Feb and need it to display just like that.

      I am connecting to an Access DB. I am trying to use the date function to set a new format for a date field in the load script: date(ACTFINISH, 'MM/YYYY') as ACTFINISH, but I get a too few arguments error. I am not sure why this is happening.

      Also, why is the date for each record displayed and not per month group?

       

      Thanks for the help!

      Juan

        • Re: Dates for Graphs
          John Witherspoon

          This is difficult to debug without seeing the relevant portion of your script and what your chart is supposed to look like.

           

          Is ACTFINISH a field in the Access DB?  Does it have the MM/YYYY format?  To get a month field out of that, I'd expect your script to look something like this:

           

          SomeTable:
          LOAD
          ...
          ,month(date#(ACTFINISH,'MM/YYYY')) as Month
          ...
          ;
          SQL
          SELECT
          ...
          ,ACTFINISH
          ...
          FROM your accesspoint table
          ;

            • Dates for Graphs

              The idea is to count a certain field in the table and then plot that against a month. ACTFINISH is a field in the table. It is short format (DD/MM/YYYY) but exported out of the source was DD/MM/YYYY general date format. I changed it to short format in Access.

               

              I used the date function previously to set a specific date format, MM/YYYY, due to errors (as per original post) are not excuted. I dont know if this is because the original source (MAXIMO) date and timestamp has an impact on the date function?

              • Re: Dates for Graphs

                The idea is to count a certain field in the table and then plot that against a month. ACTFINISH is a field in the table. It is short format (DD/MM/YYYY) but exported out of the source was DD/MM/YYYY general date format. I changed it to short format in Access.

                 

                I used the date function previously to set a specific date format, MM/YYYY, due to errors (as per original post) are not excuted. I dont know if this is because the original source (MAXIMO) date and timestamp has an impact on the date function?

              • Re: Dates for Graphs

                I have also encountered 2 additional problems (bare in mind my transaction list is about 500 000 records):

                1. I've ticked the continuous box on the grapgh display after the script was run. It seems like as soon as I tick this box, the grapgh (line) shows this hour glass and does not respond again. Surely if the script was run already, then the records should be available to display in any way or form?

                2. Seeing that I am getting issue with Access as a source I exported a table to Excel to test my luck. The Excel file was 220MB big. I could not connect to this because Qlikview gave me a Virtual Memory error.

                 

                Appreciate the help.

                 

                Juan