0 Replies Latest reply on Sep 27, 2015 6:12 PM by mehbastan

    Portal calculation

    mehbastan

      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