topic Problem with graphs using exce data in QlikView

Problem with graphs using exce data

Mon, 27 Jun 2011 16:09:02 GMT

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

Mon, 27 Jun 2011 17:48:10 GMT

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

Mon, 27 Jun 2011 17:55:19 GMT

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

Problem with graphs using exce data

Mon, 27 Jun 2011 18:07:27 GMT

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

Tue, 28 Jun 2011 00:07:26 GMT

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

Tue, 28 Jun 2011 01:27:38 GMT

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.

Problem with graphs using exce data

Tue, 28 Jun 2011 05:04:28 GMT

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.

Re: Problem with graphs using exce data

Tue, 28 Jun 2011 14:17:19 GMT

@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

Tue, 28 Jun 2011 16:42:33 GMT

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.