1 Reply Latest reply on May 11, 2017 11:05 PM by Zhouyi Zhang

    Breaking down a lending portfolio (Paid Vs. Unpaid in multiple categories)

    qicheng.chen

      So I have what is seemingly an easy question but not sure how to go about it on Tableau....

       

      Basically imagine I have a lending portfolio and a % of the balances are paid, and we categorize each paying client into specific categories as follows...

          

           

      ClientPaying CategoryOriginal BalancePaidBalance% Balance Left% of Portfolio
      ASlow2005015075%30%
      BOn Time20010010050%20%
      COn Time100505050%10%
      DPaid Off20020000%0%
      EDefault2000200100%40%

       

       

       

      Now I can make a bar chart by PAYING CATEGORY, but I need the extra step of breaking it down by payment progress... For example if I'd have a graph of (Each graph is over time so January represents a bar, then second bar is Feb, March, etc.)

       

       

      The rightside and the black line shows the overall portfolio balance decreasing, and that current stacked bar graph is showing % of the portfolio and its composition of all paying categories... Blue indicates Active clients (Slow AND On Time), whereas other colors means Default/Chargeoff/Delinquents...

       

      Now what I want to show further is that over time those "On Time" AND "Slow" clients paid % start showing up (with a new color green for example) - I know that because I'm "Coloring" it by the payment category and since they're not transitioned into "Paid off" that they will stay blue until completely changed... How would I go about this?

       

      MY IDEAL GRAPH

       

       

      My only broken solution is to do a "conditional" measure where if the account becomes paid off, take the original balance amount otherwise use the remaining balance amount, but as you know this math is somewhat flawed...

       

      Any suggestions? I'm going crazy over this I was thinking of also splitting it into "On Time PAIDOFF %, ON TIME Outstanding %, Slow Paidoff %, Slow Outstanding %, etc. etc." But then not sure how that would work in this scenario.