Discussions

Expand all | Collapse all

Formula help

  • 1.  Formula help

    Posted 22 days ago
      |   view attached
    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
    ------------------------------

    Attachment(s)



  • 2.  RE: Formula help

    Posted 22 days 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
    ------------------------------



  • 3.  RE: Formula help

    Posted 21 days 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
    ------------------------------



  • 4.  RE: Formula help

    Posted 21 days ago
    Edited by Mark Shnier (YQC) 21 days 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
    ------------------------------



  • 5.  RE: Formula help

    Posted 19 days ago
    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
    ------------------------------



  • 6.  RE: Formula help

    Posted 19 days ago
    Edited by Mark Shnier (YQC) 19 days ago
    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 Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Formula help

    Posted 19 days ago
    we aren't actually trying to import the data from our system. Importing will be done manually each week. I want to export the data we put in out in a report. Not sure if I'm making much sense.

    ------------------------------
    Courtneah Thomas
    ------------------------------



  • 8.  RE: Formula help

    Posted 19 days ago
    Well now I'm not able to picture what output you want.  I thought that you were trying to replicate the excel sheet that you posted and you would just put this field on that Admin Reporting Record.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Formula help

    Posted 19 days ago
    I basically want to do that. But I wanted to be able to pull previous weeks reports if needed. In that reporting table, it'll only show that current weeks data.






  • 10.  RE: Formula help

    Posted 19 days ago
    Well conceptually what you would do is to create a date field on the Admin record called 

    [Reporting Date]

    Then look that up down to the detail records.

    Then every place that you have used summary filters based on date, you would need to not use or the "current week" Today but you would have to use that Reporting Date field.

    You will likely find that you need some helper field to get the filters to work.  You can post back with a screen show of your filers on summary fields if you get stuck on how to convert them to use the reporting date.  You will also need ot create date fields to label some of your fields on the reporting Admin record so the user is aware which week or which the column represents.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Formula help

    Posted 19 days ago
      |   view attached
    Hey Mark,

    I have attached a "sample report" from the reporting table I created.  Its just the record form that I modified.  I can send this out by email every week but I just didn't know if the previous weeks report will be accessible or will that one record get updated on its own weekly.


    ------------------------------
    Courtneah Thomas
    ------------------------------

    Attachment(s)

    pdf
    Sample Report.pdf   1.83 MB 1 version


  • 12.  RE: Formula help

    Posted 19 days ago
    Edited by Mark Shnier (YQC) 19 days ago
    With you current design the report will change on its own each week but the previous weeks will not be accessible.

    I do have a comment on the layout.  The readability could be hugely improved by suppressing all the field headings except the first row (current week last week etc)  and moving the words like 
    Incoming to Answer and Answer to send to be beside the rows.

    Then it would look more like a tight excel sheet with row and column headings and just a tight block of numbers in a grid for the body.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 13.  RE: Formula help

    Posted 19 days ago
    That looks much better!!! Thank you for your tips and all your help!!!

    ------------------------------
    Courtneah Thomas
    ------------------------------