mehbastan

Portal calculation

Discussion created by mehbastan on Sep 27, 2015

Hi Guys

I had an Excel Sales sheet which I was tracking yearly sales for my customers ( Schools) and the following are the columns

 

2005

School Name; Gross Sales, Enrolment, Average sales; Change

 

2004

  School Name; Gross Sales, Enrolment, Average sales; Change

 

and so on

 

I just made a sales database in filemaker just like the invoice solution and have the following tables

Customer

Sales

Sales_Item

 

and they are related tables. I also made a layout for Customers and have the invoice table on that layout through a portal and have a sales_item portal on the invoice TBL

 

On the Sale_item portal I have the following fields

Sales Items

Amount

Date

 

On the invoice Portal I have

Date

Gross Sales

Avg Sales

Change

 

Everything works Ok except I need the Change in gross sales to show the difference between last year's gross and this year's Gross sales in Percentage

 

The Calculation I have is

 

Case(

Get ( RecordNumber )   >   1 and not IsEmpty ( GrossSales) ; Round (GrossSales/ GetNthRecord ( GrossSales ; Get ( RecordNumber )   -  1 ) - 1; 4 )

)

However the change shows up on the second line. What  I need is the  first line to show the difference between this year and the last which is on the second line. What am I doing wrong Please help

 

Thanks

Outcomes