4 Replies Latest reply: Jun 27, 2016 10:28 AM by Marco van Zand RSS

    Problems with TOTAL

    Marco van Zand

      Hello All,

       

      I'm working on a pipeline revenue. Got the following tables

       

      MasterCalendar

      - Date

      - Weeknr

      - Year

      - WeeknrYear

       

      Projects

      - Projectnr

      - TotalAmount

       

      Invoices Schedule

      - Projectnr

      - Date

      - Invoicenr

      - Amount

      - Percentage

       

      About the invoices Schedule the amount could be 0 with a percentage (50%) so the invoices will be TotalAmount * Percentage. At last every Project has an invoice with Amount 0 and Percentage 0 named ClosingInvoice. This is for the extra work. TotalAmount will be updated but now the invoices Schedule must calculate what the amount of the ClosingInvoice will be.

       

      Example:

      Projects

      ProjectsTotalAmount
      X100.000
      Y50.000

      Project X was 90.000 but has 10.000 extra work

       

      Invoices Schedule

      ProjectnrDateInvoicenrAmountPercentage
      X1-1150.000
      X2-1240.000
      X3-1300
      Y1-1450%
      Y3-1500

       

      The ClosingInvoice could be calculated with TotalAmount - amount already been invoiced. I use sum(TOTAL <Projectnr> Amount)

       

      What I need is as follows

       

      DateRevenue (correct)Revenues (wrong)
      1-175.00075.000
      2-140.00040.000
      3-135.000-80.000

       

      3-1 Correct:

      100.000 - (50.000 + 40.000)

      50.000 - (25000)

       

      3-1 Wrong:

      100.000 - (50.000 + 40.000 + 25.000)

      50.000 - (50.000 + 40.000 + 25.000)

       

      When I select one contract everything is working fine