Counting number of things active at multiple time points: [Help please]

Hi… I am trying to create a chart of the count of projects active each month and can’t figure out how to set it up.

Projects have start and end dates, and a range, so searching for projects that have start and end dates that overlap with a date range (say, the last year) is fine. But then I need to group the projects somehow. Using the :group by modifier only allows to select from a field, so I am a bit stuck there… I need the total number of active projects each month, not just the start or end month.

The only thing I can think of is a bit heavy: to make list field for each project in which I iterate all of the active months for the project. Then I would base my :group by on that.

I use Floppy quite a lot and maybe the answer is in iterating something there , but I am not sure…

Any advice is appreciated.

I do something similar with our charts.

First, you need to search for projects that are only active and has a created date of “current date/time: rounded down to month”

Here’s how it looks for us. Just disregard the data type and some fields.

You would then group them by creation date.

This graph would then show all the projects starting from the start of the month to the current date.

Here’s how it looks like in action:


What I like about this is that it doesn’t show all the days of a month directly. It would show the things from the start of the month down to the current date. It would then reset next month :smiley:

EDIT: This shows how many projects were created on each day, giving as a more detailed view.

To calculate how many active projects there are on each month, we just have a text element that counts all the projects by searching for the projects and adding them

You could customize the points or in this case, how many projects are there on each time frame by changing the interval and type of grouping

Thanks for your thoughts… yes, I guess that is the other ways to do it- meaning if I want to look at 12 months, I do 12 searches with start-end date range overlapping with the last year range. Would that be a bit heavy compared to iterating a list of dates and storing them in the project thing whenever the project dates change?

No. You just change the constraints to year.

Why would you store the dates whenever the project date changes? Are you planning to have a history of changes made to a project? The built in field “modified date” already updates whenever there are changes in a thing.

Thanks again @ntabs - I will have to fool around with this and will see if I can used your advice to get what I need… I can see how this works with a single date point like created date, but the list of dates / date range makes it a bit more confusing… that is what I meant by storing a list of dates in the project thing… so then I could count the number of projects that have each month in the list of dates… but I will try to follow your design first and see how it goes.

This topic was automatically closed after 70 days. New replies are no longer allowed.