Is it possible to do a weighted average inside a Summarize In-DB?

[mattd17]

Hi all,

Still fairly new to Designer, I have a fairly hefty database (several hundreds of thousands of unique loans) of a portfolio of assets that updates monthly, and I'm trying to build a summary entirely with in-DB tools. My issue is this:

1. I have a created field "pct_orig" that takes a monthly snapshot of current_loan_balance / original_loan_balance
2. I have a created field "previous_loan_balance" that references the previous month's current balance
3. I'm creating a summary using Summarize In-DB that groups my portfolio by loan_type, orig_date, orig_credit_score, loan_status and report_dt and then applies a sum of previous_loan_balance as an additional column
4. I know I can take an average when building a Summarize In-DB table, but I want my pct_orig field to be weighted by previous_loan_balance rather than a straight average. Is this possible?

Thanks for the help!

-Matt

[jdunkerley79 · Accepted solution]

I think the easiest way is to create a new column pct_orig * previous_loan_balance

Then in the summarise in-db tool create two totals one of pct_orig * previous_loan_balance and one of previous_loan_balance

Finally use a formula in-db tool to created weighted average.

[mattd17]

Thanks! That method did it.