Calculating Balance based on previous data?

[sjdig]

Is it possible to calculate a balance based on other information and have it return an updated value at each point in time?

Starting Month Balance: $800.00

Example:

Date	Account	Debit	Credit	Balance
04/01/2019	12345	0.00	5.00	805.00
04/01/2019	12345	0.00	10.00	815.00
04/01/2019	12345	17.00	0.00	798.00
04/01/2019	12345	22.00	0.00	776.00
04/01/2019	12345	29.00	0.00	747.00
04/02/2019	12345	0.00	8.00	755.00
04/02/2019	12345	27.00	0.00	728.00
04/03/2019	12345	14.00	0.00	714.00
04/04/2019	12345	0.00	26.00	740.00
04/05/2019	12345	260.00	0.00	480.00
04/06/2019	12345	0.00	500.00	980.00
04/07/2019	12345	13.00	0.00	967.00

This is just a sample of how it looks in the system I'm referencing. Credits show first low to high on each date, and then debits low to high on each date.

I have the starting month balance that can be referenced, for this example I used $800.00, but is there a way to get the balance at the end of each specific day in time? Or even, after each specific debit or credit?

So, one day one, reference starting month balance of $800.00 and then add in first credit of $5.00. However, at every subsequent level, it should now add this new balance for each transaction entry.

So:
When day 1, transaction 1 -> (Starting Month balance + first posting) I'll call this New Balance
When day 1, transaction 2 -> (New Balance + second posting) -> New Balance 2
When day 1, transaction 3 -> (New Balance 2 + third posting) -> New Balance 3


I have tried using the functions that utilize running but thus far I have had no luck.

I know how to do it in Excel but can't think of a way to do it in Cognos. Is it even possible?

[bus_pass_man]

Just to confirm, you want to have an expression which will return you something like the balance column of your table.  Is that a correct understanding?

There's newly introduced functions such as running-total, in 11.1.x.  I can't find any documentation on them online but they have some in the expression editor.  I've used a couple and they work.

I have tried using the functions that utilize running but thus far I have had no luck.

Can you elaborate on that please.

Where are you getting the $800 from?  Could it be possible that whatever you're looking for is there already?  Does it just store the monthly balances?

Where is your data?  What is its structure? If you had a table where all the transactions existed then the running-total etc. would be easy-peasy.

[sjdig]

Just to confirm, you want to have an expression which will return you something like the balance column of your table.  Is that a correct understanding?

There's newly introduced functions such as running-total, in 11.1.x.  I can't find any documentation on them online but they have some in the expression editor.  I've used a couple and they work.
Can you elaborate on that please.

Where are you getting the $800 from?  Could it be possible that whatever you're looking for is there already?  Does it just store the monthly balances?

Where is your data?  What is its structure? If you had a table where all the transactions existed then the running-total etc. would be easy-peasy.

Yes, it stores the monthly balance which will be used as the starting balance for the purposes of the report. I think what I'm having difficulty with is how to construct the running function in such a way to do what I need. I believe it's just a concept I do not yet have an understanding of.