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

    create calculated field where duplicates are filtered out of average

    Tyler Smith

      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
          swaroop.gantela

          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.

           

          Please see the attached workbook in the Forum thread,

          and please adjust it to more closely match your setup.

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

            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
              swaroop.gantela

              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
                Tyler Smith

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

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