topic Re: Weighted average in Fact table in QlikView

Weighted average in Fact table

cristian_av — Wed, 25 Nov 2020 16:16:04 GMT

I have a fact table with the stipulated payment days of customers. These days are stipulated for each operation. I have the operations in a fact table and each operation can be repeated multiple times depending on the keys of dimensions...

I need to get this number:

Avg PaymentDays=(10*2+20*1+30*1)/4

Fact Table
Customer	Operation	PaymentDays	Key1	KeyN…
A	A	10	x	…
A	A	10	x	…
A	B	10	x	…
A	C	30	x	…
A	C	30	x	…
A	C	30	x	…
A	D	20	x	…
A	D	20	x	…
A	D	20	x	…
A	D	20	x	…

A resume of the fact table is like this (Just for you to understand the calculation)

Days	Operations (Count)
10	2
30	1
20	1

How should I do this calcution?

Thanks!!

Re: Weighted average in Fact table

sunny_talwar — Thu, 17 May 2018 15:51:05 GMT

May be this

Sum(Aggr(PaymentDays * Count(DISTINCT Operation), Customer, PaymentDays)/Count(DISTINCT Operation)

Re: Weighted average in Fact table

cristian_av — Thu, 17 May 2018 16:02:32 GMT

Didn't work! It gives me a "-" as result

This expression worked:

Sum(PaymentDays * Aggr(Count(DISTINCT Operation), PaymentDays )) /

Count(DISTINCT Operation)

Thanks!