topic Re: AVG of Dates in QlikView

AVG of Dates

rodri_morales — Wed, 18 Sep 2013 14:34:08 GMT

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

CELAMBARASAN — Wed, 18 Sep 2013 14:44:26 GMT

Try something like this

avg(Aggr(DatePaymentBill-DateReceptionBill, BillID))

Re: AVG of Dates

Gysbert_Wassenaar — Wed, 18 Sep 2013 14:48:03 GMT

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

rodri_morales — Wed, 18 Sep 2013 14:54:21 GMT

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

rbecher — Wed, 18 Sep 2013 15:25:00 GMT

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

rodri_morales — Wed, 18 Sep 2013 15:28:06 GMT

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