Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Employee
Employee

Re: Weighted Average Pivot Table

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

1 Reply
Employee
Employee

Re: Weighted Average Pivot Table

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

Community Browser