Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
!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
Hahahaha how can I consider Amount to be id? Lol... anyways where do you see 0? Example selection?
Sunny,
Each trx id has a unique Total amount.
Example selection Days = -290
Thanks,
Mohan
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.
Try this
Avg(Aggr(Sum(Aggr(([Received Date] - Date)*Amount, GROUP, [TRX ID], [Received Date]))/Sum(Amount), GROUP, [TRX ID]))
Bingo,
Thanks, Sunny. Also, I got bit learning about aggr function from your expression.