8 Replies Latest reply: Jun 28, 2011 12:42 PM by Leonard Short RSS

    Problem with graphs using exce data

      I am trying to make a graph of count vs time.

       

      Time here is general - month, day of week and date.

       

      So how can i generate this general time axis? the data i am importing is from excel.

        • Problem with graphs using exce data
          Leonard Short

          Do you have a timestamp or date field in your data? Or do you need to generate an entire calendar?

            • Problem with graphs using exce data

              i need to generate a calendar from April 2011 till Dec 2011.

                • Problem with graphs using exce data
                  Leonard Short

                  If you have a date in your data table then set the vDateMin & Max to look at that field, if you have just a hard coded date then replace the =num(peek( etc... with num(date('04/01/2011'))

                   

                  LET  vDateMin = Num(Peek('Date', 0, 'tablename'));

                   

                   

                  LET vDateMax = Num(Peek('Date', -1, 'tablename'));

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   


                  TempCalendar:
                  LOAD $(vDateMin) + RowNo() - 1 AS DateNumber,
                  Date($(vDateMin) + RowNo() - 1) AS TempDate
                  AUTOGENERATE 1
                  WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)
                  ;

                    • Problem with graphs using exce data

                      i have 2 date fields. i want to display the count on the y axis, time on the x-axis. And it will be a line graph showing 1 line for opened date and 1 line for closed date.

                       

                      so acc. to this, how should i use your code mentioned above?

                       

                      Thanks!

                        • Problem with graphs using exce data
                          Leonard Short

                          I would use the open date for vDateMin and the close date for vDateMax i.e.

                           

                          LET vDateMin = num(peek('OpenDate',0,'tablename'));

                          LET vDateMax = num(peek('CloseDate, -1, 'tablename'));

                           

                          Then the TempCalendar script will generate a value for every date between the two so you would use your TempDate (or whatever you want to call it) as your x-axis and your count as the expression for the y axis.

                            • Re: Problem with graphs using exce data

                              @leonard:

                               

                              I get this error:

                               

                              Field not found - <<=>

                              TempCalendar:

                              LOAD  + RowNo() - 1 AS DateNumber,

                              Date( + RowNo() - 1) AS TempDate

                              AUTOGENERATE 1

                              WHILE +IterNo()-1<=

                               

                               

                              after i wrote this query:

                               

                              LET  vDateMin = Num(Peek('[Opened Date]', 0, '[..\My Documents\Downloads\incident(5).xls]'));

                              LET vDateMax = Num(Peek('[Closed Date]', -1, '[..\My Documents\Downloads\incident(5).xls]'));

                               

                               

                              TempCalendar:

                              LOAD $(vDateMin) + RowNo() - 1 AS DateNumber,

                              Date($(vDateMin) + RowNo() - 1) AS TempDate

                              AUTOGENERATE 1

                              WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)

                              ;

                                • Problem with graphs using exce data
                                  Leonard Short

                                  Try removing the brackets in your let statements:

                                   

                                  LET  vDateMin = Num(Peek('Opened Date', 0, '..\My Documents\Downloads\incident(5).xls'));

                                  LET vDateMax = Num(Peek('Closed Date', -1, '..\My Documents\Downloads\incident(5).xls'));

                                   

                                   

                                  Then in a textbox outside of the script add =$(vDateMin) to see what is being returned for your variables if anything. You may need to load the data from your incident table into QV before you run the peek statement against it.

                              • Problem with graphs using exce data

                                Hi,

                                 

                                Can you share sample data in Excel file and QV file as well if possible?

                                This will help you get exact solution from this community.