Previous Function

My boss wants a report to show how many requests we are receiving and closing in a month which is easy enough. He also wanted to show an Opening Balance and Closing Balance column, which is causing me problems.

Here’s a mockup of the report.

Month		JAN_OPEN_BAL	Received	Completed	Closing Balance
January  	283		100		68		315
February 			88		33		55
March    			115		56		59
April    			116		65		51
May      			99		88		11
June     			133		114		19

We don’t really have a way of going back and finding the opening balance so for January I created a Freehand query as a separate data provider hardcoding 283 and January, whcih I then linked to the other data provider.

Closing Balance is a variable and is defined as Jan_Open_Bal + Received - Completed.

What I’d like to do is create a new Opening Balance variable to get the previous month’s Closing Balance and then have the Closing Balance reference the new Opening Balance variable in it’s definition.

That doesn’t work because I get a Looping Variable Definition error.

Does anyone have a suggestion on how to do what I’m trying ? I’ve tried all types of combinations of variables but it’s not working.

Thanks.


tscoccol :us: (BOB member since 2003-11-15)

Using the example you provided. what are the results you want to see in this new variable.
If I know the results we’re after I should be able to get it working

cheers


Reporter Bloke :uk: (BOB member since 2004-05-20)

For February through June, I’d like to see the following values in the Closing Balance variable:

370 (315 + 88 - 33)
429 (370 + 115 - 56)
480 (429 + 116 - 65)
491 (480 + 99 - 88 )
510 (491 + 133 - 114)

And the Opening Balance would be the previous month’s Closing Balance. So for February through June it would be:

315
370
429
480
491

Thanks for looking into this!


tscoccol :us: (BOB member since 2003-11-15)

Sorry for the delayed reply…

I have just got the closing balance part to work: … although it’s still work in progress :slight_smile:

It might be easier if I can mail you the report

I create a report using excel data to replicate your values
Then created a table using

Next I created a local variable <1> using the formula:

=If (RunningCount()=1) Then (<Jan_open_balance>+)-

Next I inserted a second new column and used the formula:

=If (RunningCount()=1) Then (<Jan_open_balance>+)- Else RunningSum( If RunningCount()>1 Then (Previous(<1>)+<Jan_open_balance>)-)


Reporter Bloke :uk: (BOB member since 2004-05-20)

That looks interesting, I didn’t think to use the RunningCount function. Feel free to mail the report to me. If you click on the email button in one of my posts, it should give you my email address.

Thanks a lot.


tscoccol :us: (BOB member since 2003-11-15)

oh i forgot to check … v5 or v6 ?


Reporter Bloke :uk: (BOB member since 2004-05-20)

version 5.

Thanks.


tscoccol :us: (BOB member since 2003-11-15)

Hey, just wanted to let you know that your solution works like a charm!

Thanks so much for your help !


tscoccol :us: (BOB member since 2003-11-15)