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

[sunny_talwar]

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

[mohan_1105]

Sunny,

Each trx id has a unique Total amount.

Example selection Days = -290

Thanks,

Mohan

[mohan_1105]

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.

[sunny_talwar · Accepted solution]

Try this

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

[mohan_1105]

Bingo,

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