Summary Report with multiple sets of data

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered
I'm trying to create a Summary Report but I just can't get my head around the QuickBase way of doing this, I'm assuming that a Summary Report is the right way to do this, but if someone has a better idea I'd love to hear it.

I have a system support app with a table called "Calls" where we log each call received along with the "Open Date" and "Close Date".

I would like a summary table of this, that groups the calls by months and shows four sets of data:

1. Brought Forward Calls - the number of calls that were opened before the start of the month, but were not closed before the start of the month.

2. New Calls - the number of calls that were opened during the month.

3. Closed Calls - the number of calls that were closed during the month.

4. Carried Forward Calls - the number of calls that were opened before the end of the month, but were not closed before the end of the month.

2 and 3 I can create as single data set summary reports, but have no idea how to combine them into one. 1 and 4 the only way I could think of was to create a parent table to summarise.

As I said, bit stuck on this one so would really appreciate any guidance.


Photo of Paul

Paul

  • 30 Points

Posted 5 years ago

  • 0
  • 1
I suggest that you create a text formula field called [Call Status] which calculates to one of the following.

Brought Forward

New


Closed

Carried Forward



Then you can run a Summary report counting calls with the rows being the [Call Status] and the columns being the date grouped by month.

if you have any problem with parts of that formula, post back with which one(s) of the four results is giving you a problem.
Photo of Paul

Paul

  • 30 Points
I'm not entirely sure how that would work, for example a call could be opened in January and closed in February, therefore it would have a status of both New and Carried Forward in January, and a status of Brought Forward and Closed in February.
I could in theory create a Call Status text field for each month, but that's not really a practical solution.
.... and that is he problem.  I believe the only way to do his would be to have a parent table loaded up with a YYYYMM code for every month.  Then you will need relationships back to the detail to count up the # of opened calls that YYYYMM.

Then you will need another relationship to count up the next category, and so on for he four of them.