Can QB generate a report that would show a month-by-month comparison of records opened vs records closed?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I would like a chart similar to the following picture (which I generated in Excel).  
Photo of Darlene

Darlene

  • 10 Points

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
This can be done but it's complicated to explain.  Not hard to do really, but a bit complicated to explain.   As you have already realized you can't simply graph the tickets because each check it it can only appear on the chart in one place it can't simultaneously be on your redline and your blue line for tickets open and tickets close.

So the way I would do this would be to construct a parent table with a key field in the format like

Opened -1
Opened -2
Etc going back 12 months

Then similarly,
Closed -1
Closed -2
Etc going back 12 months.

 Then I'm your child table of tickets I would have a calculated field that would calculate to that same value based on how many months back from today the ticket was either open or close.

Then I would make a relationship between the parent summary table and the tickets  and I would summarize a count of the number of tickets.

 You should then be able to construct a graph where the series is based on a calculated field which is extracted out of the Key field that would calculate either to the word opened or closed.

 Initially, your graph would not lately show the month number or name.

So then what you would likely want to do would be to create a calculated field so that the time series on your ex access will sort properly.    So for example you would probably want to calculate a value for the YY – MM  and use that to be the x-axis of your chart.

Hopefully that gives you a start to your thinking process and if you need one on one assistance to actually get this working you can contact me off-line via the information in my profile. We could probably get it all set up in about an hour.
Photo of Darlene

Darlene

  • 10 Points
Thanks Mark!  I think you may have been using voice recognition software to type, because some words came through a little strange ("well they should ship" I think is probably "one relationship"?) ... but I was able to follow the key line of reasoning and could see how this would work.  Since this report is not an essential need, I'm not going to pursue it at this time, since I don't want to add additional tables to my application, but I learned something new and thank you for your response!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
Yes, sorry, I was talking to my iPad.  I have gone back and corrected the typos.