Working with time-phased data

Time-phased values are daily slices of data, rather than cumulative. When you add up the values for all the days between a task's start and its finish, this will equal the total value for that task.

Tables in a Business Intelligence export model that contain time-phased data must declare the slicing value in the table definition, as in the following example:

Only functions which support a date range in formulae can be time-phased. Any inclusion of a non time-phased item in a time-phased section will result in multiple returns of its total value which must be treated accordingly, and for non time-phased functions by using planned percent complete * fullmeasureofX to get a linear division of the value over a date range.

For example, a ten day task would report a planned percent complete of ten for each working day, and zero for the weekends.

If you are targeting a pivot table as your output you must provide defaults for any missing values, particularly if referencing baselines, as in the following example:

if(Task[~CB].PlannedPercentComplete(LP, na) > 0 ,Task[~CB].PlannedPercentComplete(LP, na) * Task[~CB].duration,'0d')

Try(Task.PlannedPercentComplete(CB, na) * Task[~CB].duration,'1d')

Manipulating time-phased data

Time-phased data gives you daily values for each item or resource. This is perfect if you want to report on or drill down to individual resources, but most commonly you will want to look at cumulative totals for a project across a date range.

Manipulating time-phased data In SQL

In SQL, simply sum the attribute and group by date. It may be worth creating a view if the SQL view is time-consuming to populate. Create a table populated by stored procedure after the data is gathered.

After summing the data, run a secondary query or use the running total options in your report tool to turn the dates into a cumulative series.

Manipulating time-phased data in Microsoft Excel

Carrying out the same operation in Microsoft Excel® is quite tricky. Here is one possible way of achieving it:

Insert a pivot table:
Select the columns to sum the totals for each day in the date range:
Calculate the cumulative total, using the following formula pasted down the height of the column:

=Sum(FixedPosition to Relative position) i.e =Sum(A$1:A1)

This will add from the first cell to the second, then the first to the third etc, all the way down. Unfortunately this calculation is exponentially more expensive as you add days, so if you wish to use large date ranges you will suffer a significant performance hit.

If performance is an issue, you could try adding the previous cumulative total to the date total, ie =D3+F2, where F is the cumulative total and D the day total.
With the dates and cumulative totals now present, you can add a simple graph, as in the following illustration:
Finally, add a graph to display the cumulative total by date.