Formula for FY calculations

[jlhart76]

I have a report with 12-month rolling data. I need a formula to calculate the average # consumers for the fiscal year while still showing data for 12 months. (average the number of consumers if the date is July 1 or later).

 

[RobbieB]

One way would be to create a couple of variables.

to calculate the number of customers in a fiscal year (say fiscal year is 1/1/#### to 12/31/####) you could do something like this. This would give you a report total. If you want the total in the group level you will have to rearrange where you put the formulas.

put this formula in the report header.

whileprinting records;
numbervar C;
C := 0


Then put this formula in the detail ( where this formula goes could depend on how you have your report grouped)

if {oder.orderdate} in (01/01/2004) to (12/31/2004)
then
C := C+1

Then put this one in the report footer

whileprinting records;
numbervar C;
C:=C

Then you could create a similar series of formulas to calculate your average customers for fiscal year to date. Just change the date range to (01/01/2005) to today.
hope this works for you.