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
Try this
Avg(Aggr(Sum(Aggr(([Received Date] - Date)*Amount, GROUP, [TRX ID], [Received Date]))/Sum(Amount), GROUP, [TRX ID]))
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
Hi Mohan
Please try using the below expression
Avg(((Date - Receipt Date)*Amount)/Total amount Received)
Many Thanks
Karthik
Hi Karthik,
I have tried your expression still no luck. For your convenience I have attached the qvf file. Please consider.
Thanks,
Mohan
Can you share your Excel file?
Hello Sunny,
Excel file is now attached to this thread.
Thanks,
Mohan
This seems to work
Avg(((RECEIPTDATE-Date)*AMOUNT)/TOTAL_AMOUNT)
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.
I see no trx id in your attached app. No idea what you are talking about
Sunny,
Consider Total_amount as trx id.