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?

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?

• ###### 1. Re: How can I add days from one object to a date from another?

Dan,

You can aggregate the INVOICE_DATE  with the  Attribute function ATTR.

If you want to add a number of days to a date you can use the  DATEADD function.

So you could try something like;

DATEADD('day',sum([AR_PAYMENT_VIEW].[AVG NUMBER_OF_DAYS]), ATTR( [INVOICE_DATE] ) )

Michel