topic Re: Create chart from dates range in QlikView

Create chart from dates range

Thu, 01 Aug 2013 18:30:54 GMT

Hello,

I have a table with 3 fields: id, startDate and endDate

id	startDate	endDate
1	08/01/2013	08/05/2013
2	08/04/2013	08/07/2013

y-axis: number of ids between the startDate and endDate.

x-axis: days.

This is the chart I need. I want to count the number of ids that are between the startDate and endDate.

Any ideas on how I can do this?

Thank you,

Juan.

Re: Create chart from dates range

swuehl — Thu, 01 Aug 2013 19:35:11 GMT

Create the interval of your dates in the script:

Set DateFormat = 'MM/DD/YYYY';

Load id,

startDate,

endDate,

endDate - startDate as interval

FROM ....;

Then create a chart with dimension inteval and count(id) as expression.

Re: Create chart from dates range

Thu, 01 Aug 2013 19:49:11 GMT

Hi swuehl,

The dimension is not the number of days between the endDate and startdate. It should be the days in that interval, from 1 to 7. The purpose is to know how many ids are active each day of the month.

Re: Create chart from dates range

swuehl — Thu, 01 Aug 2013 20:22:01 GMT

Maybe like this:

LOAD *,

WeekDay(startDate+iterno()-1) as weekday,

Date(startDate+iterno()-1) as date

INLINE [

id, startDate, endDate

1, 08/01/2013, 08/05/2013

2, 08/04/2013, 08/07/2013

] WHILE startDate +iterno()-1 <= endDate;

Then use weekday or num(weekday) as dimension and count(id) as expression.