I am looking for assistance with building a reporting application for our agency.
What we are doing now is logging data from different categories into an excel spreadsheet for the current week and then we are manually inputting the data from the previous weeks.
What I'd like to do is create a report weekly that has the data from the past 3 weeks, the current year to date summary and the previous year to date. I have attached an excel document that breaks down everything.
Full disclaimer, I consider myself an amateur QB builder so go easy on me :)
------------------------------
Courtneah Thomas
------------------------------
Question
Formula help
+3This topic has been closed for replies.
+24I will assume that you have an app with a ton of detail data in it and are looking to summarize it into a nice format like you attached.
.... it a bit of project, but to replicate this form you will want to make a Table called, say, Reporting and enter one and only one record in it. That record will be [Record ID#] = 1.
Then make a formula numeric field in your details table called [Link to Reporting (=1)] . The formula will be just
1
Then make a relationship where One Reporting has many Details based on that field [Link to Reporting (=1)]
Now, you have to make a bunch of summary fields, but it starts with being able to at least make 1. So let's start with the top left.
911 Incoming to Answer this week. You need to make a summary field on that relationship and summarize the the Average for filters on 911 and Incoming to Answer and the date rage will be "during the current week" or perhaps you really want 'During the Previous 1 week".
When it comes to the field for two weeks ago, there is a "trick".
You will want to filter where
all of
Date is during the Previous 2 weeks
Date is not during the Previous 1 weeks
so think back to Venn diagrams in school.
See if you can get one or two of those fields working, then after that it's really just copying the field and editing it to make the next field. So that single relationship will have a ton of Summary fields.
When it comes to comparisons to last year, you will need to make summary fields for last year (even if they are not displayed on the form) and then do the math in a formula field to divide to get the comparison of the the current vs last year % change.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
.... it a bit of project, but to replicate this form you will want to make a Table called, say, Reporting and enter one and only one record in it. That record will be [Record ID#] = 1.
Then make a formula numeric field in your details table called [Link to Reporting (=1)] . The formula will be just
1
Then make a relationship where One Reporting has many Details based on that field [Link to Reporting (=1)]
Now, you have to make a bunch of summary fields, but it starts with being able to at least make 1. So let's start with the top left.
911 Incoming to Answer this week. You need to make a summary field on that relationship and summarize the the Average for filters on 911 and Incoming to Answer and the date rage will be "during the current week" or perhaps you really want 'During the Previous 1 week".
When it comes to the field for two weeks ago, there is a "trick".
You will want to filter where
all of
Date is during the Previous 2 weeks
Date is not during the Previous 1 weeks
so think back to Venn diagrams in school.
See if you can get one or two of those fields working, then after that it's really just copying the field and editing it to make the next field. So that single relationship will have a ton of Summary fields.
When it comes to comparisons to last year, you will need to make summary fields for last year (even if they are not displayed on the form) and then do the math in a formula field to divide to get the comparison of the the current vs last year % change.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.