Forum Discussion

CourtneahThomas's avatar
CourtneahThomas
Qrew Member
4 years ago

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 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 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's avatar
      CourtneahThomas
      Qrew Member
      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's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        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
        ------------------------------