Line graph showing count of projects completed by plan vs actual completion date

[tschullo]

I have a conundrum I have a set of project data that contains a plan and actual completion date.

I would like to show a line graph that shows the count of projects by month, a line for plan and another for actual.

I tried using the plan  date as the x axis, and then using  the following expression for the Actuals count:

Count({<[ProjCompl MonYr]=p([Planned ProjCompl MonthYr])>} [Project ID])

However this is not working.

If anyone has a solution, please let me know.

Thanks!

[tschullo · Accepted solution]

My script solution is to create a table that brings in both dates into one that I can use as an X-axis:

RPT_DATES:
LOAD PROJ_ID,
[Planned ProjCompl MonYr] as RptMonYr,
[Planned ProjCompl Qtr] AS RptQtr,
1 as PlanDt
Resident PROJECT_DATA
WHERE Not IsNull([Planned ProjCompl MonYr]);
Concatenate(RPT_DATES)
LOAD PROJ_ID,
[ProjCompl MonYr] as RptMonYr,
[ProjCompl Qtr] AS RptQtr,
1 as ActualDt
Resident PROJECT_DATA
WHERE Not IsNull([ProjCompl MonYr]);

Then in the line graph I use RptMonYr (or RptQtr) as my x-axis
and sum(PlanDt) for one line
and sum(ActualDt) for the other.

This works fine,