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?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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?
Photo of Katy Scott

Katy Scott

  • 84 Points 75 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
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.


Matthew Neil - Product Specialist
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
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. 
Photo of Katy Scott

Katy Scott

  • 84 Points 75 badge 2x thumb

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?



Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 63,962 Points 50k badge 2x thumb
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
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
Here is my bias vote.

https://www.mcftech.com/