Discussions

Expand all | Collapse all

Add Field Total for Date Range

  • 1.  Add Field Total for Date Range

    Posted 9 days ago
    Hello,

    We are trying to create a field, not a report, that adds up the Principal Balance for a given month. 

    Each record is a loan. So for example, the new field name would be January Funded and the field would display the total principal balance for all loans booked in January for every record.

    February Funded would display the total principal balance for all loans booked in February for every record - so on and so forth.

    For reference - the date field we would use is [Loan Processing Date], principal balance field is [Original Principal]

    We need this in a field for several calculations we are trying to accomplish. Any ideas how we can get this to work?

    TIA!

    ------------------------------
    Gelo
    ------------------------------


  • 2.  RE: Add Field Total for Date Range

    Posted 9 days ago
    So if you had a loan booked in January 2019 and another one booked in January 2020 and another one booked in January 2021, would they all be added into the same total?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Add Field Total for Date Range

    Posted 9 days ago
    Hi Mark,

    No, those are three different totals. For the purpose of our calculations, having the current year number is most important. If there is a way to distinguish year by year that would be great but not the end-all-be-all of our purpose. 

    Hope this helps, thanks!

    ------------------------------
    Gelo
    ------------------------------



  • 4.  RE: Add Field Total for Date Range

    Posted 9 days ago
    It is unclear to me where you want these totals to reside. Perhaps what you need is a table of months which are loaded up from excel for the next 20 years.  The key field could be a date field of the first day of the month.  Then you can have a relationship back to the load record where the reference field is a formula for the first day of the month.

    But I am not really understanding the business purpose of your request right now. If I understood that better I might be able to suggest other solutions.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Add Field Total for Date Range

    Posted 9 days ago
    One of the main purposes is for commission for sales reps. We have a budget every month. We need to reference the Budget fields to see if January is over or under budget and then pay accordingly. Ideally, we will create a field called "Commission Total" that applies the commission formula to the total amount funded for that month (For example- If Jan Funded < Jan Budget, Jan Funded * 0.005) 

    Until we know what the number is for each month, this is impossible for us to figure out

    ------------------------------
    Gelo
    ------------------------------



  • 6.  RE: Add Field Total for Date Range

    Posted 9 days ago
    Is the budget per sales rep?  Or overall for the company.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Add Field Total for Date Range

    Posted 9 days ago
    The budget is overall for the company

    ------------------------------
    Gelo
    ------------------------------



  • 8.  RE: Add Field Total for Date Range

    Posted 9 days ago
    Then I suggest setting up the table where the individual months are listed like I described above. Just enter a month or two to see if you can get the relationship working.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Add Field Total for Date Range

    Posted 8 days ago
    Hi Gelo,

    I would second Mark's suggestion of building a month's table, this would help you to easily see your budget each month in one place and be able to run reports on those aggregate numbers outside of a summary report. We have an article Quick Base Analytics: Improve your monthly insights by implementing a Months table that walks through setting up a months table in QB to get insights like this that might help you get started and understand how you would set something like this up.

    ------------------------------
    Evan Martinez
    ------------------------------