Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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.

8 Replies
Not applicable

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?

Not applicable

Problem with graphs using exce data

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

Not applicable

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)
;

Not applicable

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!

Not applicable

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.

Not applicable

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.

Not applicable

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)

;

Not applicable

Problem with graphs using exce data

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.