Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Weighted Average Pivot Table

Hi everyone,

I have a pivot table with 5 dimensions, and partial sums activated for all of the dimensions. I would like to add an expression for weighted average of interest rates for the loans in my table (based on the outstanding balance). It would look something like this:

sum((outstanding_balance/sum(total outstanding_balance))*interest_rate)

Then, when you add the individual values of this expression together for each loan you should get the weighted average of interest rate for all the loans.

I added this expression to my pivot table, but the confusion lies in the partial sums of the dimensions. If I'm not mistaking, the only total on my chart that would give me an accurate reading of weighted interest rate is the total that adds the entire column. Is there a way that I can get an accurate weighted average for the partial sums of the dimensions as well?

Thanks

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

I think you will get the right numbers also in the partial sums if you use

     Sum(outstanding_balance*interest_rate) / Sum(outstanding_balance)

This will calculate the average interest rate, but weighted by the balances.

HIC

View solution in original post

1 Reply
hic
Former Employee
Former Employee

I think you will get the right numbers also in the partial sums if you use

     Sum(outstanding_balance*interest_rate) / Sum(outstanding_balance)

This will calculate the average interest rate, but weighted by the balances.

HIC