Forum Discussion
QuickBaseCoachD
7 years agoQrew Captain
The reason that you are struggling is that when you make a summary report the underlaying record (a project) can only appear in one "cell" if the summary report.
A solution is to create a series of fields such as
[Project is Active Current Month]
[Project is Active Current Month +1]
[Project is Active Current Month +2] etc
you can go out say 12 months.
Then you would just run a simple table report to get your totals.
The formula would be like this for the field
[Project is Active Current Month +2]
(not tested)
var date ActiveMonthStart = FirstDayOfMonth(AdjustMonth(Today(),2));
var date ActiveMonthEnd = FirstDayOfMonth(AdjustMonth(Today(),2));
[Project Start Date]<= $ActiveMonthEnd
and
[Project End Date] >= $ActiveMonthStart
// adjust the 2 for each successive copy of the field.
A solution is to create a series of fields such as
[Project is Active Current Month]
[Project is Active Current Month +1]
[Project is Active Current Month +2] etc
you can go out say 12 months.
Then you would just run a simple table report to get your totals.
The formula would be like this for the field
[Project is Active Current Month +2]
(not tested)
var date ActiveMonthStart = FirstDayOfMonth(AdjustMonth(Today(),2));
var date ActiveMonthEnd = FirstDayOfMonth(AdjustMonth(Today(),2));
[Project Start Date]<= $ActiveMonthEnd
and
[Project End Date] >= $ActiveMonthStart
// adjust the 2 for each successive copy of the field.