Finding Weighted Average Coupon

[CMartinez]

Hi all,

I am new to Qlik, so getting used to the syntax to create expressions. I am working on a table in Qlik Sense and attempting to add a column (I assume a Measure) that reflects the Weighted Average Coupon (WAC) of a specific subset of loans from my data.

WAC = [(UPB*Interest Rate)+(UPB1*Interest Rate1)+(UPB2*Interest Rate2)+...] / Total UPB of the subset.

My dimension in place on the table is labeled as 'Investor' (field = MORT_TYPE_DESC).

I am wanting to display the WAC for each subset of the investor label. I have the UPB (field = CURR_UPB) and Interest rate (field = CURR_INT_RATE) of each loan's data loaded.

What I need the expression to do is to calculate [ ((CURR_UPB) * (CURR_INT_RATE)) + ...] **for each loan in the subset**, then that value is to be divided by the total sum of CURR_UPB values of that given subset. 

This is my very *unknowledgeable* attempt:

Sum(Aggr(Sum({<CURR_UPB>}*{<CURR_INT_RATE>}))/Sum(TOTAL{<CURR_UPB>}),MORT_TYPE_DESC)

Appreciate any assistance. Thanks!

[marksouzacosta · Accepted solution]

Hi @CMartinez ,

Before mixing your Measures, I would recommend decomposing them so you can understand the effects of each of the parts, so later you can combine them.

In Qlik Sense, by default, all your measures in charts works in the context of your Dimensions - unless you specify the opposite. For instance, if you just add this measure Sum(CURR_UPB) it will Sum all CURR_UPB for each MORT_TYPE_DESC value.

Play with those Measures, you can add multiple measures in that chart, just to study the formulas.

Measure 1: Sum(CURR_UPB)

Measure 2: Sum(TOTAL CURR_UPB)
Note the effect of the TOTAL Statement in your values

Measure 3: Sum(CURR_UPB * CURR_INT_RATE)

Measure 4: Sum(CURR_UPB * CURR_INT_RATE)/Sum(TOTAL CURR_UPB)
This is the one you are probably looking for. You can keep only this measure at the end.

Please let us know if you are getting close to what you are looking for.

Regards,

Mark Costa

[CMartinez]

Hi Mark,

I appreciate the response. I walked through your process to see the differences each step made. Really appreciate the breakdown. I seemed to have over complicated it in my initial attempt.

I was getting a difference in the end result when checking the result from Qlik vs my end result doing the equation in Excel using the same data. After revisiting the steps in your breakdown, I found that the formula I needed was the below:

Sum(CURR_UPB * CURR_INT_RATE)/Sum(CURR_UPB)

Taking out the TOTAL for the equation allowed it to use the correlating summed UPB for that subset, which matched the outcome I should be seeing according to my calculation. I appreciate the help!