Number Being Repeated in Next Group

[hoagieryder]

I have a report set up so with two columns, data from one Date and Data from a second date. This report has a number of groups as well. For each date there are 2 summaries, balance and weighted average rate. I am running into a problem with the summaries. If the balance is 0 (only in the 12/31/2008 date) then the previous rate from the previous group is being reported. It should however be 0. Here is an example:

3/31/2009 12/31/2008
Balance Rate Balance Rate
2345 5.45 3455 6.32
3344 5.62 0 6.32


Since weighted avg. rate is based on balance, and balance = 0, I want the weighted average rate to be 0.
What would cause something like this?

 

[lbass]

Are you using formulas or running totals for the balance and rate? What are the content of these? Is this a crosstab or have you use multiple column formatting?

-LB

 

[hoagieryder]

?asofdate = 3/31/2009
Also if nonAccrualCode = "Y" then the loan should have a 0 rate.


Balance for 3/31/2009 =
If {?asofdate} = {loans.asOfdate} then {loans.BalanceOwned} else 0

Rate for 3/31/2009 =
weighted average of balance new and ratenew
ratenew = If {loans.nonAccrualCode} = "Y" or {loans.asOfdate} = dateserial(year({?asofdate}),month({?asofdate})-2,1)-1 then 0
else
{loans.curIntRate}

Balance for 12/31/2008 =
If dateserial(year({?asofdate}),month({?asofdate})-2,1)-1 = {loans.asOfdate} then {loans.BalanceOwned} else 0

Rate for 12/31/2008 =
weighted average of balance old and rateold
rateold = If {loans.nonAccrualCode} = "Y" or {loans.asOfdate} = {?asofdate} then 0
else
{loans.curIntRate}

I also have a variance column that shows the difference between the old dates and the new dates. I just noticed that when I remove the variance data, everything looks fine, when I include it the numbers get messed up. I have a different formula for each group variance but here is an example of one for group SORTER...

variance balance = Sum ({@BalanceNew}, {@SORTER})-Sum ({@BalanceOld}, {@SORTER})

variance rate = WeightedAverage ({@newrate}, {@BalanceNew}, {@SORTER})-WeightedAverage ({@oldrate}, {@BalanceOld}, {@SORTER})

 

[lbass]

Are you using variables in any of these nested formulas? It looks like you are hitting a null and then the previous value is populating. You will need a reset formula in this case.

-LB