Looking for a single cell formula for a G/L account(s) total of last twelve month end balances without creating 12 separate G/L function formulas for each month end summed together. Basically looking to use in a yield formula for trailing twelve month yield on various loan product earnings. Need this to be a Jet Reports solution, not Jet Analytics or other offering.
Any assistance would be appreciated.
Thank you.
as in =GL("Balance","4000","2023-08-01","2024-07-31") to get the sum of GL trx for account 4000 from August 1 2023 to July 31 2024?
or do i misunderstand the question?
unless its moreso =GL(Balance,4000,,2024-07-31)+GL(Balance,4000,,EOMONTH(2024-07-31,-1))+GL(Balance,4000,,EOMONTH(2024-07-31,-2))+GL(Balance,4000,,EOMONTH(2024-07-31,-3)) ... etc
set 2024-07-31 to a report option cell
Appreciate the response and question, thank you. To your point, yes, I have the formula for g/l account(s) activity over a period of time, what I'm looking for is a "short-cut" formula for the average of each month end balance for prior twelve months for yield calculation. So instead of chaining twelve g/l formulas for each respective month end, can I use nl(sum) or some combination to provide the correct result. I have used =GL("Balance","1200",,"2024-08-31")+GL("Balance","1200",,"2024-07-31")+GL("Balance","1200",,"2024-06-31")+GL("Balance","1200",,"2024-05-31")+...GL("Balance","1200",,"2023-09-31")/12 but prefer an alternative solution if possible.
Thanks again!
i see. yes that a tough one.
if it was me i think id put them in 12 separate columns (or rows), and hide the columns/rows with this so the user cant see it, and use an excel formula to determine the average for presentation. This is how i handed my financial ratios calculations (average ar, inv, ap, etc)
I think when u chain multiple functions like that in Jet (GL + GL + GL, etc) it is also a performance killer (if u run into optimization issues that is...)
sry not more help. interesting problem to be sure.
I have used hidden rows/columns to provide the period end balance to arrive at average or other related financial ratio needs, just looking for alternative options to minimize maintenance and or updating if new accounts need to be added or other factors are introduced.
Appreciate the tip on chained functions, I didn't think about impact on performance, that is always a consideration for sure.
Thank you.
I don' get the maintenance problem at the moment. Because you have to put the month in each formula?
I would use the twelche columns in combination with the Date Table - NL("Columns","Date","Period End", ...) - in your GL-function you can refere to the cell with the date.
I have a german version so you have to translate but you should find the field names for the Table "Date" in the function wizard.
By maintenance meaning updating the rows or columns any time g/l accounts change.
All of my formulas use report labels or shared data from an info, inputs or other references collage tab.
There are input cells highlighted in yellow which drive results for all formula based cells, whether text or number.
The Jet Report templates I create do not require I "run", the information is updated during a refresh, each time workbook is opened, etc.
If there is a need to have a dynamic based report, i.e. income statement for every business segment, I use the run feature with nl("columns"), nl("sheets") and nl("rows") as needed.
Thanks.
That's what I thought.
So the table "Date" should help you with that, isn't it?