# Discussions

View Only

## Each record has a start date and end date. How do I build a table or graph, that shows total # of records open at the end of each month?

• #### 1.  Each record has a start date and end date. How do I build a table or graph, that shows total # of records open at the end of each month?

Posted 03-30-2018 23:38
Each record has a start date and an end date. How do I build a report, either table or graph, that shows me the number of records still open at the end of each month?

• #### 2.  RE: Each record has a start date and end date. How do I build a table or graph, that shows total # of records open at the end of each month?

Top
Contributor
Posted 03-31-2018 19:42
If I'm following correct, we need to be able to report of all the records that where closed in previous months, and any records that close this month.  Additionally you'd like to include in the current month report any records that don't have a close date, thus still 'open'.

If that is the case (or close to it) you will want to make a 'formula date' field, call it something like [Month Closed].  That field will either reflect the first day of the month it was 'closed' or the first day of this month if it is still open.

If(IsNull([Close Date]),
FirstDayOfMonth(Today()),
FirstDayOfMonth([Close Date])
)

Once you have this new [Month Closed] from the formula, you can make any reports you'd like and easily have good groups and sorting display easily.

mneil@mcftech.com Phone: 440-201-6050 ext. 476" width="300px">

• #### 3.  RE: Each record has a start date and end date. How do I build a table or graph, that shows total # of records open at the end of each month?

Top
Contributor
Posted 03-31-2018 20:13
I don't think that this can be done with a simple formula.  The reason is that any particular record could be open (hypothetically) for say 10 years, so it needs to be counted in the month end count for 120 months.

But a record can only appear in a summary report or chart once - it can't summarize up into all of say 120 buckets.

So that either needs a scripting solution to create child records for each month end date per rcord that a record is open or it can also be done natively with some ingenuity.

Either way it is not so easy to do for those without scripting skills, so if you really wanted a solution to this one it would need to be important enough with work with a QSP developer to solve.

There are new native tools now such as Automations which might be coaxed into being part of a native solution as they can run saved table to table imports and hence could be a tool to create multiple children.

• #### 4.  RE: Each record has a start date and end date. How do I build a table or graph, that shows total # of records open at the end of each month?

Posted 04-02-2018 21:45

Thank you for your prompt reply. This has been and will continue to be something we want to report on. How do I engage with a QSP developer to solve?

• #### 5.  RE: Each record has a start date and end date. How do I build a table or graph, that shows total # of records open at the end of each month?

Top
Contributor
Posted 04-02-2018 22:30
You can contact me via my website contact info QuickBaseCoach.com

There is a complete list of QSPs here  https://www.quickbase.com/partners/find-a-partner">https://www.quickbase.com/partners/find-a-partner">https://www.quickbase.com/partners/find-a-partner

Top
Contributor