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

tschullo — Tue, 19 Jul 2022 14:47:42 GMT

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!

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

tschullo — Tue, 19 Jul 2022 20:30:18 GMT

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,

topic Line graph showing count of projects completed by plan vs actual completion date in App Development

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

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