I have created a saved search that produces a trial balance using the transaction record. The search includes the following:
Beginning Balance (all transaction up through the prior last day of the month)
Debits (sum for current month)
Credits (sum for current month)
Net Change (difference between sum of debits for current month and sum of credits for current month)
Ending Balance (account.balance)
I am getting inconsistent results for my beginning balance. I am using the following:
SUM(CASE WHEN {trandate} BETWEEN TO_DATE('1/1/1986','MM/DD/YYYY') AND trunc({today},'MM')-1 THEN {debitamount} ELSE 0 END) - SUM(CASE WHEN {trandate} BETWEEN TO_DATE('1/1/1986','MM/DD/YYYY') AND trunc({today},'MM')-1 THEN {creditamount} ELSE 0 END)
The results I am getting: sometimes there is value and sometimes it is blank. If I pull out each sum total separately, meaning sum the credits and sum the debits, there is always a value in one of those.
Any thoughts on why this would be inconsistent? :h_a_w: