I would like a chart similar to the following picture (which I generated in Excel).
 10 Points
Posted 3 years ago
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 70,354 Points
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 xaxis 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 offline via the information in my profile. We could probably get it all set up in about an hour.
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 xaxis 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 offline via the information in my profile. We could probably get it all set up in about an hour.
 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!
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 70,354 Points
Yes, sorry, I was talking to my iPad. I have gone back and corrected the typos.
Related Categories

Data import & export
 736 Conversations
 21 Followers

Reports & charts
 3138 Conversations
 99 Followers