Expression for counting ongoing project

[#1]

     Hello,

     I need help with the following expression which will be used in a bar chart depicting ongoing projects count(project), however, in order      to provide a time filter I would like an expression to account for a project as long as it is active in any of the 6 milestones. For eg.

     Project X should only be counted once for 2011 and Project Y should be counted for 2012,2013 and 2014.

     Alternatively, I'm not sure if this would be achieved by a expression at the list box level?

     Thanks all.

     Sarosh

Project	MIlestone 1	MIlestone 2	MIlestone 3	Milestone 4	MIlestone 5	Milestone 6
X	1/1/2011	2/1/2011	6/1/2011	1/1/2011	2/2/2011	3/1/2011
Y	1/1/2012	2/1/2012	6/1/2012	6/1/2013	6/1/2013	6/1/2014

[swuehl]

You could try something like this in your LOAD script:

SET DateFormat = 'M/D/YYYY'; // or 'D/M/YYYY'??

CROSSTABLE (Milestone, MilestoneDate)

LOAD Project, [Milestone 1], [Milestone 2], [Milestone 3], [Milestone 4], [Milestone 5], [Milestone 6]

FROM YourTableSource;

Now you have transformed to three fields, Project, Milestone, MilestoneDate.

Now eiter link you milestone dates to a master calendar, create additional date fields in your single table or use a straight table /listbox with a calculated dimension:

=Year(MilestoneDate)

and expression:

=Count(DISTINCT Project)

[#3]

Many thanks, this is new territory for me. But will try what you have stated and let you know how I make out.