1 Reply Latest reply on Jan 22, 2018 4:57 PM by Esther Aller

    Record with null end date: How to put count month over month

    Alex Dumlao

      Hello There,

       

      Does anyone know how to put a count for each month a record was open over a period of time? I've searched through the forums and couldn't find anything similar. For example:

       

      • A record has a start date in the month of January, 2017
      • Same record does not yet have an end date
      • I want to put a count of 1 for each record in each month the record was open (did not have an end date)
      • I want to display each month of January 2017 through December 2017 a count for each record that remains "open" over each month up until it has an end date
      • End goal is to have each record have a count of 1 for each month it flowed through in it's life cycle up until it's end date
        • Visually I know what I want to show: A Bar Graph for the total number of records that were "open" month over month January 2017-December 2018, with a line over the bars for the total number of records created in each month (that's easy and I have that) along with another line over the bars for the total number of records completed in each month (that's also easy and I have that
        • I know how to do the visualization, I am just at a loss on how to put a count in each month for each record through it's life cycle. The only audit logs I have on these records is the submit date (start date) and a completed date (end date) but the life cycle of these records flow over many months before they reach a completed date (end date)

       

      I have a ton of records that have a start and  do not yet have an end date. I'm at a loss when trying to put a count on each month each record was still open in between the start/end dates.

       

      Thank you for any assistance,

       

      Alex