Prior Year Formula

[jchewsmith]

I am trying to create a field called prior year and make it one less year than my paramater field {?Year}
then use the formula field to call a previous year total:


if {SSRpt_SalesGLwJE.BATCH PERIOD} = {?Month} and {@PriorYear} = {SSRpt_SalesGLwJE.BATCH YEAR}
then {@CurrentMonth Adj}

I tried using {?Year}-1 but it doesn't recognize the year in the new formula even though it displays the prior year = 2,008.00

 

[lbass]

So what is the content of {@PriorYear}? What do you meant it "doesn't recognize the year"? Is your batchyear field a number or a string? Do you get any error messages?

-LB

 

[jchewsmith]

What I have in the report is a month by month list of numbers:

1-2008 50,000
2-2008 25,000
1-2009 13,000
2-2009 10,000

In the group footer I am trying to create formulas that show current year month info, then prior year month info, then current YTD info, then Prior YTD info.

I create a formula that says if month = 01 and year = {?Year} then xxxx (getting 13,000)
Then I create a formula that says if month = 01 and year = {?year}-1 then xxxx (wanting to get: 50,000) but I get 0 instead. I also tried creating a formula called prior year and entered {?year}-1.00 then use prior year in the other formula instead of {?year}-1, but still only get a 0?

Thanks for your help.

 

[lbass]

Please show the actual formulas, not descriptions of them. Based on your description the formula should work, so there must be something we don't yet know about the formula. What fields are you working with? Is there a month field? Are the month and year fields strings? What fields are you grouping on?

-LB

 

[jchewsmith]

Thank you for your help. I finally got it to work by creating a running total field and using the prior yea formula in that, then using the running total to get the result. I'm still not sure why it wouldn't work, but I found a work around.

I really appreciate your help.

Thanks