Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a dataset with a task_number with a start and end date. I would like to display this in a chart with the x-axis being the date. My purpose is to show the open and closed task over time.
The data looks like:
Task Number Task Created Date Task End Date Task Status Open Tasks Max Date
21412826 26.8.2009 Open 11.1.2010
21600258 15.9.2008 17.9.2008 Closed
If a Task is Closed it has an End Date if not it has an Open Tasks Max Date. So the open task with TaskNumber 21412826 will be recorded with one record from 26.8.2009 to the 'Open Tasks Max Date' field which is 11.1.2010. The Closed Task Number 21600258 will be recorded on the 15.9.2008, 16.8.2008 and 17.8.2008 (the Task End Date).How can I achieve this?
Regards,
Rishi
Have a look here: http://community.qlik.com/forums/t/25279.aspx
Similar kind of logic should work for you. Just a quick thought.
Rakesh
Hi Rakesh,
I already set the max and min date. I would like to have in one table every date between the start and end date.
Rishi
Hi Rishi,
Rakesh pointed you to right place, the code is available there to generate the dates between a min and max date. That should suffice, isn't it. Check the code starting:
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATETempDate AS EstProjFin,
1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Hi Vidyut and Rakesh,
Thanks for the direction. I have now created two tables, one with the tasks and start and end dates and the other the Calendar with all the possible values from the Tasks table.
How do I populate the dates between the start and end date with the values from the Calendar for every Tasknumber recorded?
I am sorry being kind of a newbie in Qlikview...
Regards,
Rishi
hi
attach is your file
with one more step , an interval match function
Thank you very much guys for the input!
It works neat!
Regards,
Rishi
Hi,
Use this,
LET vDateMin = Num(AddYears(Today(),-1));
LET vDateMax = Num(Today());
LET NoOfDays = $(vDateMax) - $(vDateMin);
TempCalendar:
LOAD
Num($(vDateMax) - recno() + 1) as CalendarDateNum,
date($(vDateMax) - recno() + 1) as CalendarDate
AUTOGENERATE
$(NoOfDays);