Formula help
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 ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendI 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
------------------------------
- Mark let me start by saying YOU ARE A GENIUS!!! I really really really appreciate your help!!!!
Okay.....I got everything to work with the exception to last years YTD. In that field, I know one filter needs to be date is during the previous 1 year. The "and" filter is what I'm stuck on. If I am trying to get YTD data let's say from 1/1/2020-7/15/2020, what filter would we use for that?
------------------------------
Courtneah Thomas
------------------------------- MarkShnier__YouThx for the genius comment. I think I'm just hooked in brain teasers.
Ys, that one is more tricky. Let me think.
Here is one solution that should work, but not tested.
Make a new formula date field called [Date Plus 1 Year]. The formula will be
AdjustYear([My Date Field], 1)
So now we have last year's date for the MM DD part, but with the current YYYY.
Then filter where
all of
[Date Plus 1 Year] is during the Current Year
[Date Plus 1 Year] is before Today
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------- Hi Mark.
Update. IT WORKED!!!!!!
My very last question. How would we actually get the data into a report? The idea is to run the report weekly with the updated data.
------------------------------
Courtneah Thomas
------------------------------
