When writing the Balance Sheet report there is a line item called Profit Period. There are 2 ways to calculate the profit period:
1: All GL's that belong in Revenue, COGS, and Expenses to obtain the Net balance. This is a great way to perform a check & balance against the General Ledger
2: Take all Assets - all Liabilities - all Equity=Profit Period
I'm able to get the calculation in and it works as expected until I we get to 12/31/xx. At the end of each year we have to close each GL for the year. In doing so, all GL's that belong in Revenue, COGS, and Expenses have to be zeroed out to begin the new year with $0. That end of year value gets moved into an Equity GL 3 account which is on the Balance Sheet.
In order to obtain the correct Profit Period calculation throughout the year, I have to exclude any transactions that have a particular transaction type. In this case, I exclude transtype 'Ending Balance'.
The problem now resides on that last transaction. The End of Year transaction "Ending Balance" gets moved from Revenue, COGS, and Expenses GLs into an Equity GL - Retained Earnings. This movement gives the transtype 'Ending Balance', which is what I have to exclude to obtain the correct aggregation for the rest of the year/months.
In the current state of my widget, I need that final EOY number BEFORE it gets moved into the Equity GL account. Writing this query in SQL is no problem at all, however, Sisense wants to return the cumulative number and not that yearly number. I can even isolate this 1 line entry for each year within SQL. However, Sisense is still relatively new to me & I’m unable to obtain the correct calculation for that end of year date 12/31/xx. This number has to be yearly calculations not the cumulative number from the begging of time.
Any help is greatly appreciated.
Please sign in to leave a comment.