Weighted Averages in Qlik Sense

[#1]

Hey guys,

I'm trying to create a measure which multiples the number of existing titles against a fee amount, and then divides this calculated amount against the number of rows of that particular title. Basically I'm trying to distribute the fee equally to each company holding that particular title, which I think is a weighted average.

Title	Company	Fee
A	Company A	$900
A	Company B	$900
A	Company C	$900
B	Company A	$1000
B	Company C	$1000
C	Company B	$400
D	Company C	$1200
D	Company D	$1200

For example, Company A would result as so:

Count(Title) * Fee

Count(A) * 900

1 * 900 = 900

900 / 3 = 300

Count(Title) * Fee

Count(B) * 1000

1 * 1000 = 1000

1000 / 2 = 500

So Company A's Total would be 800 instead of 1900.

I thought the correct expression would be this:

(Count(Title) * Fee) / Aggr(Count(Title),Title)

But it doesn't seem to be working for all rows and I'm stuck on what to do.

Thanks in advance to anyone who figures it out!

EDIT:

Hey guys sorry for opening this up again but I'm still having problems.

After trying out the solution I've discovered that it still doesn't work how I like it to, I don't think I did a good job of explaining (didn't include the Title_Type table) so I'll try again.

I have 2 tables:

Title_Type	Fee
AA	$900
BB	$1000
CC	$800

Title	Title_Type	Company
A	AA	Company A
A	AA	Company B
A	AA	Company C
B	BB	Company A
B	BB	Company C
C	AA	Company B
D	BB	Company C
D	BB	Company D

Now for example if Title_Type AA has a fee of $900 and Title A has 3 companies sharing it, Company A should have 1/3 of that fee and have $300.

And then as Title_Type BB has a fee of $1000 and Title B has 2 companiies sharing it, Company A would have 1/2 of that fee and have $500.

The total then would be $800 for Company A.

The current formula which has been suggested:

Sum(Current_Fee) / Count(distinct Company)

Doesn't seem to be working for this. Instead it's summing up the fees of titles without repeating summing up the fees of recurring title_types.

What I need it to do is divide the fee for the title by the number of company's with that same title, and then add up all calculated fees.

Sorry if I haven't explained this well. Thanks to anyone who can solve this problem for me!

[hic]

With the above table as source data, you will have problems. The $900 of Title A will be counted three times since there are three records. And this is not what you want.

If you instead have one table with Title & Fee - one record per title - and one table with Title & Company, it becomes a lot easier:

Then you can use Title as dimension and

     Sum(Fee)/Count(distinct Company)

as measure.

HIC

[#3]

Thanks! It appears to be working at the moment but I haven't put in my set analysis yet. I'll have to check back tomorrow. Thanks again!

[#4]

Very sorry Henric, that solution isn't working for me but that's most likely due to how badly I explained my problem.

I edited my post to hopefully better explain what I'm trying to do. Hope you can help!

[hic]

This is a case where I think it is better to do the calculations in the script. Basically, you want to create a table where you calculate the fee for each combination of Title and Company. So...

[Price List]:
Mapping Load * Inline
[Title_Type, Fee
AA, 900
BB, 1000
CC, 800] ;

tmpBreakdown:
Load * Inline
[Title, Title_Type, Company
A, AA, Company A
A, AA, Company B
A, AA, Company C
B, BB, Company A
B, BB, Company C
C, AA, Company B
D, BB, Company C
D, BB, Company D] ;

Left Join (tmpBreakdown)
Load
Title,
Count(Company) as NumberOfCompanies
Resident tmpBreakdown
Group By Title;

Breakdown:
Load *,
ApplyMap('Price List',Title_Type,0) / NumberOfCompanies as Fee
Resident tmpBreakdown;

Drop Table tmpBreakdown;

[#6]

Hi Henric,

I'm very sorry I should have clarified, the data I posted in this thread was just example data to simplify explanation and conceal confidentiality. The tables posted above belong to two excel spreadsheets (the second sheet containing about 1000 rows) which I've loaded into Qlik Sense.

Unfortunately the method you've provided does not work for me for this reason, but I am still a very early beginner to all this and am still learning, and every bit of help you give me helps me learn further so I thank you for that!