14 Replies Latest reply: Nov 11, 2017 11:21 PM by Mohan Kumar

# How to calculate the weighted Average for receivables?

!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.

Mohan

• ###### Re: How to calculate the weighted Average for receivables?

Hello Qlikers,

Do we have anything on this to do?

or

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

Mohan

• ###### Re: How to calculate the weighted Average for receivables?

Hi Mohan

Please try using the below expression

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

Many Thanks

Karthik

• ###### Re: How to calculate the weighted Average for receivables?

Hi Karthik,

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

Thanks,

Mohan

• ###### Re: How to calculate the weighted Average for receivables?

Can you share your Excel file?

• ###### Re: How to calculate the weighted Average for receivables?

Hello Sunny,

Excel file is now attached to this thread.

Thanks,

Mohan

• ###### Re: How to calculate the weighted Average for receivables?

This seems to work

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

• ###### Re: How to calculate the weighted Average for receivables?

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

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

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.

• ###### Re: How to calculate the weighted Average for receivables?

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

• ###### Re: How to calculate the weighted Average for receivables?

Sunny,

Consider Total_amount as trx id.

• ###### Re: How to calculate the weighted Average for receivables?

Hahahaha how can I consider Amount to be id? Lol... anyways where do you see 0? Example selection?

• ###### Re: How to calculate the weighted Average for receivables?

Sunny,

Each trx id has a unique Total amount.

Example selection Days = -290

Thanks,

Mohan

• ###### Re: How to calculate the weighted Average for receivables?

Hi stalwar1 ,

I hope the attached excel explains clearly what I am expecting to do. Kindly help me to get the value.

Thanks for your time so far.

Mohan.

• ###### Re: How to calculate the weighted Average for receivables?

Try this

Avg(Aggr(Sum(Aggr(([Received Date] - Date)*Amount, GROUP, [TRX ID], [Received Date]))/Sum(Amount), GROUP, [TRX ID]))

• ###### Re: How to calculate the weighted Average for receivables?

Bingo,

Thanks, Sunny.   Also, I got bit learning about aggr function from your expression.