6 Replies Latest reply on Nov 9, 2018 3:17 AM by Jim Dehner

    LOD & Duplicates

    Wendi Williford

      Hi Tableau Community,

       

      I'm having an issue with duplicate values in a dataset that has Borrower IDs, Loan IDs, And Principal Outstanding for each month. I'm trying to calculate the amount of principal outstanding at the end of each quarter, but duplicate values are severely inflating this number.

      I've usually been able to solve for this issue using the FIXED calculation, but am having trouble here - I think maybe because I'm aggregating the principal amount incorrectly?

       

      The Q4 2018 number in the "Principal Outstanding" field is correct in the attached, but none of the other quarters are. My attempt at calculating the correct amount is the "Calc'd Principal Outstanding" field.

       

      Any help is greatly appreciated.

       

      Capture.JPG

        • 1. Re: LOD & Duplicates
          Don Wise

          Hi Wendi,

          If I bring in [ACH Date] to your fixed calc, then the calculation lines up with your Q4-2018 number for Principal Outstanding, but the other numbers for the remaining quarters do not. However, the ratios are much closer to Principle Outstanding than before...not sure if that's what you're looking for in terms of numbers?  Thx, Don

           

          Screen Shot 2018-11-08 at 11.37.39 AM.png

          • 2. Re: LOD & Duplicates
            Deepak Rai

            Hi Wendi

            Check If It Correct Now?

            Thanks

            Deepak

            • 3. Re: LOD & Duplicates
              Jim Dehner

              Hi Wendi

              I believe the issue is in the logic - the outstanding principal is by its nature a running number - i.e. it is a point in time number - - the value you want is the outstanding principal at 3/31, 6/30. 9/30 and 12/31 --- not the sum of the end of month values over the quarter -

               

              you can do that with a conditional statement that looks at the max date by loan by the last month for each quarter (you don't have an end of month entry in your file) -

               

              does that make sense?

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              1 of 1 people found this helpful
              • 4. Re: LOD & Duplicates
                Wendi Williford

                Yes, that mostly makes sense.

                What would this conditional statement look like? I've tried a few using IF and DATETRUNC, but am still having trouble getting the principal outstanding amount.

                I added an end of month calc (the "EOM") field.

                • 5. Re: LOD & Duplicates
                  Wendi Williford

                  Did this simple conditional statement and it worked.

                   

                  IF DATEPART('month',[Month]) = 12 THEN [Principal Outstanding]

                  ELSEIF DATEPART('month',[Month]) = 9 THEN [Principal Outstanding]

                  ELSEIF DATEPART('month',[Month]) = 6 THEN [Principal Outstanding]

                  ELSEIF DATEPART('month',[Month]) = 3 THEN [Principal Outstanding]

                  ELSE 0

                  END

                  • 6. Re: LOD & Duplicates
                    Jim Dehner

                    I'm glad to help out -

                    you could also just filter for the 4 EOQ months

                    Either way it works

                    Jim