Add Field Total for Date Range

Question

Hello,We are trying to create a field, not a report, that adds up the Principal Balance for a given month.&nbsp;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------------------------------

markshnier__you · Answer

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

gelo · Answer

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.&nbsp;Hope this helps, thanks!------------------------------Gelo------------------------------

markshnier__you · Answer

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

gelo · Answer

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 &lt; Jan Budget, Jan Funded * 0.005)&nbsp;Until we know what the number is for each month, this is impossible for us to figure out------------------------------Gelo------------------------------

markshnier__you · Answer

Is the budget per sales rep?&nbsp; Or overall for the company.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Forum Discussion

Add Field Total for Date Range

8 Replies