How to calculate the weighted Average for receivables?

[mohan_1105]

!Hi Qliker,

I am calculating the weighted average of date difference by an amount for any dimensions. The formula I wanted to use is,

Days = Sum((Date - Receipt Date) * Amount) / Total amount received.

The expression I used is as follows,

Avg(Aggr((sum(Aggr(((num(RECEIPT DATE) - num(Date))*AMOUNT),AMOUNT)) / TOTAL_AMOUNT),AMOUNT,%Dimension))

There is a mismatch with the arrived value.

After an excel workout, I found that, if the "AMOUNT" and "TOTAL_AMOUNT" are same the expression gives 0 as the value.

I couldn't understand what went wrong to fix the expression to get a correct value.

Thanks in advance !!

Mohan

[mohan_1105]

Hello Qlikers,

Do we have anything on this to do?

or

should I need to put the question in any other way?

Thanks in advance !!

Mohan

[karthikoffi27se]

Hi Mohan

Please try using the below expression

Avg(((Date - Receipt Date)*Amount)/Total amount Received)

Many Thanks

Karthik

[mohan_1105]

Hi Karthik,

I have tried your expression still no luck. For your convenience I have attached the qvf file. Please consider.

Thanks,

Mohan

[sunny_talwar]

Can you share your Excel file?

[mohan_1105]

Hello Sunny,

Excel file is now attached to this thread.

Thanks,

Mohan

[sunny_talwar]

This seems to work

Avg(((RECEIPTDATE-Date)*AMOUNT)/TOTAL_AMOUNT)

[mohan_1105]

Sunny,

This expression is valid when I have a single receipt date for a trx id. In some cases, data has multiple receipt date. For those cases, I have to add the weighted avg, to be more clear,

(receipt date 1 - date)*Amount

+ (receipt date 1 - date)*Amount

-----------------------------------

=Addition

Addition / Total received amount = My Average.

I tried to replicate the same with the expression it workes fine for the trx id having multiple received date, but for the trx id with a single receipt date expression gives the value 0.

[sunny_talwar]

I see no trx id in your attached app. No idea what you are talking about

[mohan_1105]

Sunny,

Consider Total_amount as trx id.