1 Reply Latest reply on May 16, 2013 10:31 AM by Michel Caissie

    How can I add days from one object to a date from another?

    Dan Gerena

      I have 2 objects:

      Open_Invoices list all open invoices, the respective customer, the amount due and an invoice date...

       

      For instance:

      Invoice#    Customer   Invoice Date   Amount Due

      12345        ABC            1/1/2013          50.00

      12346        DEF            1/14/2013        100.00

      12347        ABC            2/3/2013          11.00

      etc.

       

      Days_to_Pay lists the average dats it takes to pay each customer based on past history...

      Customer  Avg Days to Pay

      ABC          33

      DEF          15

      etc.    

       

      When I join the objects together (based on Customer) my desired result is to add the Avg Days to Pay to each record's respective Invoice Date, to compute an "Estimated Date"...like this:

      Invoice#    Customer   Invoice Date   Amount Due Avg Days to Pay  Est Date

      12345        ABC            1/1/2013          50.00               33                      2/2/2013

      12346        DEF            1/14/2013        100.00               15                     1/29/2013

      12347        ABC            2/3/2013          11.00               33                      3/3/2013

      etc.

      But when I create this calculation after joining the tables:

      [INVOICE_DATE]+([AR_PAYMENT_VIEW].[AVG NUMBER_OF_DAYS])

      I get an error in the Calcualtion Dialog:

      "All fields must be aggregate or constant when using table calculation functions"

      when I change the calc to this:

      [INVOICE_DATE]+sum([AR_PAYMENT_VIEW].[AVG NUMBER_OF_DAYS])

      I get this error:

      "Cannot mix aggregate and non-aggregate arguments to function"

       

      So how do I accomplish?