5 Replies Latest reply: Sep 18, 2013 11:33 AM by Rodrigo Morales

# AVG of Dates

How can I get the AVG between the day the bill come and the day we paid the bill?

I make this:  =num(avg(day(DatePaymentBill-DateReceptionBill)), '##,##')

But I have a problem, sometimes we paid before the bill come (like a prepayment) and other times we paid after the bill come. So I wanna make 2 text object with this average.

• ###### Re: AVG of Dates

Try something like this

avg(Aggr(DatePaymentBill-DateReceptionBill, BillID))

• ###### Re: AVG of Dates

Try these two

Pre-paid:

=num(avg(if(DatePaymentBill<DateReceptionBill, DateReceptionBill -DatePaymentBill)), '##,##')

Paid afterwards:

=num(avg(if(DatePaymentBill>=DateReceptionBill, DatePaymentBill-DateReceptionBill )), '##,##')

• ###### Re: AVG of Dates

I do that, but I think it isn't the correct value. AVG(DatePaymentBill-DayReceptionBill) returns me the average in days?

• ###### Re: AVG of Dates

What else you are expecting? There is no average date.. However, you could add the avg days to the DayReceptionBill to get the expected DatePaymentBill if this makes sense.

• ###### Re: AVG of Dates

I want to know if the difference between DatePaymentBill and DateReceptionBill is in days. If not, I have to add the condition day()

I have this:

Without Prepayment

=num(avg(if(DatePaymentBill>=DayReceptionBill, DatePaymentBill-DateReceptionBill)), '##,##')

With Prepayment

=num(avg(DatePaymentBill-DateReceptionBill), '##,##')

If I add the function day() to the avg, the values are incorrect, because Without Prepayment < With Prepayment and thi isn't correct. Always AVG with Prepayment must be less than AVG without Prepayment