Total Monies Between Dates 1

[LDickenson]

Hi

I need to put a few formulas in a report. I have two fields:-

Total Fees
Date Fees Billed

I need to add to a report

TOTAL FEES YEAR 1 TOTAL FEES YEAR 2 TOTAL FEES YEAR 3 TOTAL FEES YEAR 4

I have been trying to write a formula but I am having trouble getting it to work - any tips?

Thanks

Lisa

 

[IanWaterman]

If you group report by date fees billed. In third drop down "This section will be printed" select for each year.

You can then do a sum for that group.


Ian

 

[LDickenson]

Hi Ian

Thanks, I need to acutally use financial year dates and not full years, i.e. I need to do:-

30th May 2011 to 1 April 2012
30th May 2010 to 1 April 2011

Thanks,

Lisa

 

[IanWaterman]

Your can create a formula but you will need to update report as formula runs out of dates. Group on this formula

@FinYear
If DateFeesBilled in [date(2010, 05, 30) to date(2011, 04,01] then 'Year1' else
If DateFeesBilled in [date(2011, 05, 30) to date(2012, 04,01] then 'Year2' else
If DateFeesBilled in [date(2012, 05, 30) to date(2013, 04,01] then 'Year4' else
If DateFeesBilled in [date(2013, 05, 30) to date(2014, 04,01] then 'Year5' else
.....

Repeat until bored or a point at which you will have left the company ;-)

Alternatively you can create a Calendar table which holds all dates and has a column identyfing FinYear. You can then group on this table.

Ian

 

[LDickenson]

Thanks Ian

Much appreciated.

Lisa.

 

[lbass]

The following {@FY} would return the correct fiscal year:

"FY "+totext(year({table.date}+216),"0000")

You could then use {@FY} in a crosstab report as the column field, with amounts as the summary field.

-LB