Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Weighted arithmetic mean avoiding duplicates

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Anonymous

Not applicable

2017-11-22
07:12 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

sunny_talwar

MVP

2017-11-22
07:21 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May be this

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

8 Replies

2017-11-22
07:19 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How about this?

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

2017-11-22
07:19 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Or this?

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

sunny_talwar

MVP

2017-11-22
07:21 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May be this

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

Anonymous

Not applicable

2017-11-22
07:26 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

712 Views

2017-11-22
07:40 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Expected result from given data?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

712 Views

Anonymous

Not applicable

2017-11-22
07:53 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

712 Views

sunny_talwar

MVP

2017-11-22
07:55 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2017-11-22
08:54 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!