topic Re: How to calculate the weighted Average for receivables? in App Development

How to calculate the weighted Average for receivables?

mohan_1105 — Sat, 11 Nov 2017 05:29:33 GMT

!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

Re: How to calculate the weighted Average for receivables?

mohan_1105 — Sat, 11 Nov 2017 06:39:23 GMT

Hello Qlikers,

Do we have anything on this to do?

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

Thanks in advance !!

Mohan

Re: How to calculate the weighted Average for receivables?

karthikoffi27se — Sat, 11 Nov 2017 07:58:52 GMT

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?

mohan_1105 — Sat, 11 Nov 2017 09:25:09 GMT

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?

sunny_talwar — Sat, 11 Nov 2017 13:03:45 GMT

Can you share your Excel file?

Re: How to calculate the weighted Average for receivables?

mohan_1105 — Sat, 11 Nov 2017 13:14:53 GMT

Hello Sunny,

Excel file is now attached to this thread.

Thanks,

Mohan

Re: How to calculate the weighted Average for receivables?

sunny_talwar — Sat, 11 Nov 2017 13:23:55 GMT

This seems to work

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

Re: How to calculate the weighted Average for receivables?

mohan_1105 — Sat, 11 Nov 2017 15:06:39 GMT

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.

Re: How to calculate the weighted Average for receivables?

sunny_talwar — Sat, 11 Nov 2017 15:38:48 GMT

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?

mohan_1105 — Sat, 11 Nov 2017 15:44:51 GMT

Sunny,

Consider Total_amount as trx id.

Re: How to calculate the weighted Average for receivables?

sunny_talwar — Sat, 11 Nov 2017 15:50:21 GMT

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?

mohan_1105 — Sat, 11 Nov 2017 16:08:16 GMT

Sunny,

Each trx id has a unique Total amount.

Example selection Days = -290

Thanks,

Mohan

Re: How to calculate the weighted Average for receivables?

mohan_1105 — Sat, 11 Nov 2017 17:29:17 GMT

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?

sunny_talwar — Sat, 11 Nov 2017 18:27:31 GMT

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?

mohan_1105 — Sun, 12 Nov 2017 04:21:22 GMT

Bingo,

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