# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for
Did you mean: Anonymous
Not applicable

## Weighted arithmetic mean avoiding duplicates

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. 1 Solution

Accepted Solutions  MVP

May be this

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

8 Replies  MVP

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)  MVP

Or this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)  MVP

May be this

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

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  MVP

Expected result from given data?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context) Anonymous
Not applicable
Author

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).  MVP

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)) Anonymous
Not applicable
Author

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!  Community Browser