I have 3 files, Customer - Invoice- InvoiceLine.

I want to define a calculated field in the customer file which is the total of the sales or invoice values for the current financial year. In the Invoice file, the Invoice_Value is Sum(Invoice|InvoiceLine:Invoice_Line_Value).

I tried the following

Calculated field in Invoice file to isolate invoices that fall in the financial year ; (European date format)

If(Invoice_Date>31/04/2002 and Invoice_Date<01/05/2003,Invoice_Value,0)

I would then define another sum() field in the customer file, using the Customer ID relationship.

However, it doesn't work, always returning 0.

Any suggestions on how to achieve the result appreciated.

Andrew

Try this one...

If(Invoice_Date > Date(4,31,2002) and Invoice_Date < Date(5,1,2003),Invoice_Value,0)

or have a look at the Sample file I posted in the Sample Section (Handy Bits). It has a calc in there for determining in which Financial year a date lies. Then you can do sub-summary reports by FY.