topic Re: Weighted arithmetic mean avoiding duplicates in QlikView

Weighted arithmetic mean avoiding duplicates

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

Hi all,

I need to do a weighted arithmetic mean something like: (a*weight(a)+b*weight(b) +....) / (weight(a)+weight(b) +...). I am able to do this but the problem are the duplicates. I will explain further:

Product line, Year-Quarter, Subregion, Weight, Target

Alpha, FY17-Q4, Spain, 12, 50

Alpha, FY17-Q4, Portugal, 15, 70

Alpha, FY17-Q4, Norway, 13, 80

Alpha, FY17-Q4, Dennmark, 18, 60

I want to represent the Target of the Region Europe( Iberia(Spain+Portugal) + North Europe(Norway+Dennmark) ), so i need to do: (12*50+15*70+13*80+18*60) / (12 +15+13+18).

To do this i have this expression:

sum(Target * Weight)/sum(Weight)

This is not working, i think that becose of the duplicates. I tried to use Distinct in the load script but it is not working (the scripts is huge and messy, someone made it a long time ago and left that monster as a present).

Is there a way to make the sum but only one value of Target and Weight for each SubRegion??

Thanks for the help,

I can explain further if needed.

Re: Weighted arithmetic mean avoiding duplicates

Anil_Babu_Samineni — Wed, 22 Nov 2017 12:19:10 GMT

How about this?

Sum(TOTAL (Weight * Target)) / Sum(TOTAL Weight)

Re: Weighted arithmetic mean avoiding duplicates

Anil_Babu_Samineni — Wed, 22 Nov 2017 12:19:58 GMT

Or this?

Sum(TOTAL <Subregion> (Weight * Target)) / Sum(TOTAL <Subregion> Weight)

Re: Weighted arithmetic mean avoiding duplicates

sunny_talwar — Wed, 22 Nov 2017 12:21:51 GMT

May be this

Sum(Aggr(Target * Weight, [Product line], [Year-Quarter], SubRegion))/Sum(Aggr(Weight, [Product line], [Year-Quarter], SubRegion))

Re: Weighted arithmetic mean avoiding duplicates

Anonymous — Wed, 22 Nov 2017 12:26:00 GMT

Either of them is working. I think that the problem is that for each Subregion there are around 200 duplicates of the weight. If i could only use 1 I think it would work. I also tried Sum(DISTINCT (Weight * Target)) / Sum(DISTINCT Weight) but sometimes the weights have the same value so it causes problems.

Thanks,

Lluís

Re: Weighted arithmetic mean avoiding duplicates

Anil_Babu_Samineni — Wed, 22 Nov 2017 12:40:56 GMT

Expected result from given data?

Re: Weighted arithmetic mean avoiding duplicates

Anonymous — Wed, 22 Nov 2017 12:53:37 GMT

This: (12*50+15*70+13*80+18*60) / (12 +15+13+18).

As I said the problem is that there are many duplicates of the weights for each subregion (I dont understand where they come from).

Re: Weighted arithmetic mean avoiding duplicates

sunny_talwar — Wed, 22 Nov 2017 12:55:13 GMT

Did you give this a shot?

Sum(Aggr(Target * Weight, [Product line], [Year-Quarter], SubRegion))/Sum(Aggr(Weight, [Product line], [Year-Quarter], SubRegion))

or this

Sum(Aggr(Target * Weight, SubRegion))/Sum(Aggr(Weight, SubRegion))

Re: Weighted arithmetic mean avoiding duplicates

Anonymous — Wed, 22 Nov 2017 13:54:43 GMT

This totally worked!! Thanks!!

I will leave this link just in case someone (like i did) needs further info about Aggr Aggr() function

Thanks toboth of you!