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

# 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

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

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

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:

TempCalendar:

LOAD  + RowNo() - 1 AS DateNumber,

Date( + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE +IterNo()-1<=

after i wrote this query:

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

Try removing the brackets in your let statements:

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?