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------------------------------

I 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 just1Then 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 whereall ofDate is during the Previous 2 weeks Date is not during the Previous 1 weeksso 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 ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.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------------------------------

Thx 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 beAdjustYear([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 ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.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------------------------------

Ok, great.Help me understand the issue. Do you have some massive data source which can coax out a dump of the detailed data? And you are asking how to make this easy (like ideally in the category of stupid simple) to import into the app regularly.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Formula help | Qrew Discussions

12 Replies

MarkShnier__You
Qrew Legend
4 years ago
I 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
------------------------------
- CourtneahThomas
  Qrew Member
  4 years ago
  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__You
    Qrew Legend
    4 years ago
    Thx 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
    ------------------------------

Forum Discussion

Formula help