Urgent Cumulative Actual/Forecast Chart

[#1]

Hi All,

I have wasted like 3 days on this and I am now desperate for help. I am having trouble wrapping my head around this one...I am trying to get to an end product like the following

Ignoring the Plan line (since that is the easiest), this is a cumulative count of tasks by date. The actual is supposed to represent data up until now, the forecast is representing data into the future. They need to be two seperate lines so they show in the legend as follows. In the backend I have done the following:

TaskEDTCompletion:

//Forecast:

LOAD HHEDTIDM,

ExpectedinVersion as DTETasksCompleted,

1 as TaskEDTMeasureID

RESIDENT EPMTasks

WHERE ExpectedinVersion>=today(0)

AND IsNull(ExpectedinVersion)=0

AND IsNull(HHEDTIDM)=0;

//Actual:

concatenate (TaskEDTCompletion)

LOAD HHEDTIDM,

TaskActualFinishDate as DTETasksCompleted,

2 as TaskEDTMeasureID

RESIDENT EPMTasks

WHERE TaskActualFinishDate<=today(0)

AND IsNull(TaskActualFinishDate)=0

AND IsNull(HHEDTIDM)=0;

The problem is in the chart now I do not know how to write a formula such that my cumulative actual line ends as of this month, and my forecast line begins when this ends. I have DTETasksCompleted as the dimension and the expressions I have so far are

Actual:

rangesum(COUNT({$ <TaskEDTMeasureID={2}>} DISTINCT HHEDTIDM),above(COUNT({$ <TaskEDTMeasureID={2}>} DISTINCT HHEDTIDM),1,rowno()))

Forecast:

rangesum(COUNT({$ <TaskEDTMeasureID={1}>} DISTINCT HHEDTIDM),above(COUNT({$ <TaskEDTMeasureID={1}>} DISTINCT HHEDTIDM),1,rowno()))

This is only giving me the following:

I feel like I am almost there, but I can't figure out where to go from here - I need the blue line to end in Mar 2011, and the red line to start where the blue line ends. Any time I try to add any condition like DTETasksCompleted<=ReloadTime() it doesn't seem to work....

Any ideas or suggestions?

[pat_agen]

Hi juliaford,

I take it a task is either completed (TaskActualFinishDate<=today(0) AND IsNull(TaskActualFinishDate)=0) or is forecast ExpectedinVersion>=today(0) AND IsNull(ExpectedinVersion)=0)?

so forecast = actuals for everything less than today?

could you not play with the TaskEDTMeasureID field. If this field is bigger than 1 you are dealing with an actual if it is bigger than 0 you are dealing with actuals+forecast.

forecast can then be:

rangesum(COUNT({$ <TaskEDTMeasureID={1,2}>} DISTINCT HHEDTIDM),above(COUNT({$ <TaskEDTMeasureID={1,2}>} DISTINCT HHEDTIDM),1,rowno()))

the lines should follow each other till today so depending on how you order them you can get the actuals to overlay the forecast.

currently your actuals flat line after today which you don't want. During the load script you can calculate the maximum date for actuals task. then use this in your actuals calculation to render null if you are past the maximum actual date.