4 Replies Latest reply on May 16, 2018 5:12 AM by Tyler Smith

# create calculated field where duplicates are filtered out of average

Hello,

I am having trouble getting a function to work correctly...

if IsNULL([Qualified Credit Score])=false

then {FIXED [Loan Number]: AVG([Qualified Credit Score])}

END

as you can see, the goal here is to display the average credit score, filtering out Null values and only using credit scores for a loan number once (each loan has 1 to many credit scores and right now my calculation is averaging all of them).

what I have written doesn't seem to be filtering the loan number

any help is greatly appreciated, Thanks!

• ###### 1. Re: create calculated field where duplicates are filtered out of average

Tyler,

Apologies, I would be grateful for clarification on the averages.

The average score per loan should work as you described [AvgPerLoan]:

{ FIXED [Loan Number]:AVG([Score])}

If you are seeking to get the average of all those average scores,

you can use:

{AVG([AvgPerLoan])}

It looks like this already takes into account the Nulls, handles duplicates,

and gives the correct average of the averages.

• ###### 2. Re: create calculated field where duplicates are filtered out of average

Swaroop,

This is why I am so confused, I thought the calculation I wrote would work

right now it is averaging like this

Loan num                         name                         score

123                              Jake Smith                    655

123                      CO  Jill Smith                       655

122                              John Doe                      612

122                     CO   Jane Doe                      612

122                    CO     Josh Doe                    612

AVG: 629.2

I would want it to exclude one of the co borrowers for each loan number, The weird thing is if I do the population count code I have, it gives me the correct total of borrowers (2) that I would like for this calculation. I have not been able to extract any info from that calculation that would help with this one.

• ###### 3. Re: create calculated field where duplicates are filtered out of average

Tyler,

Thank you for the example data.

Please see if the attached is closer to the goal.

It does seem to take more steps than I would have thought. Maybe there's a more direct way.

Using only Level of Detail calculations, can get one Score for each Loan Number using:

{ FIXED [Loan Num]:AVG([Score])}

Then Count the number of Loan Numbers:

{COUNTD([Loan Num])}

Then can get the average as

{SUM([LOD Score Per Loan Num])}/[LOD Number of Loans]

This can also be done using window calculations:

One Score per Loan number:

IF FIRST()=0 THEN SUM([Score]) END

//with compute using of Pane Down

Then get the average using:

WINDOW_AVG([One Score per Loan Num])

//With compute using of all Specific Dimensions, Restarting None

//Nested Calculation compute using of Pane Down

• ###### 4. Re: create calculated field where duplicates are filtered out of average

I was able to get the code below to work yesterday:

avg(int({ INCLUDE [Loan Number]: avg([Qualified Credit Score])}))