I am wondering what is the best way to compare subsets of data to each other and to the greater set of data.
I am trying to build a cumulative loss chart based on a portfolio of loan assets. I downloaded Lending Club data to use as an example.
I am trying to build a chart of curves which shows cumulative loss rates by loan age as a percentage of the total size of the total loan portfolio. The X-axis has the number of months after loan inception, and the Y-axis is the percentage of the portfolio. Each curve represents a pool of loans which is determined by the date of inception. I.E., One curve represents all loans that were originated in 2007 Q1, another curve for 2007 Q2, etc.
Has anyone seen an example of something similar?
I have a snapshot of a current loan portfolio, which is made up of loans originated over time, some of which are in good standing as well as some that have charged-off (defaulted). For each of the charged-off loans, I am able to determine the number of months after inception that they have defaulted (or incurred a loss), and what is the amount of such loss.
I have attached my current Tableau workbook with the data loaded, as well as an excel sheet with the same data in which I was able to accomplish the task above (see the second tab of the excel workbook).
But I cannot figure out how to do this in Tableau, and I cannot find any examples.
Any help would be immensely appreciated.
Thanks so much!
Mark
Message was edited by: Mark Smith
Hi Mark,
I think you can generate your view pretty easily with Tableau. Below are the steps I would take.
1. Create a field called Months from Issue Date: DATEDIFF('month', [Issue D], [Close Date])
2. Create a custom date field from Issue_d.
3. Create a LOD called Funded by Q: { FIXED [issue_d (Quarters)] : SUM([Funded Amnt]) }
4. Create a field called Default %: SUM([Def Prin Amt])/SUM([Funded by Q])
5. Drag Months from Issue Date to Columns, Default % to rows and your custom date to colors.
6. Change Default % to a running total
7. Then just filter for the Quarters you want.
Let me know if you have any questions.
Regards,
Ivan
