Does anyone know the best way to reference an LOD from one table when working with data that is blended from another table?
I am working with a pool of loans. The data has been downloaded from Lending Club and is all publicly available.
I have two tables:
1 - The first table, called "LoanTape" is a snapshot of each loan with some characteristics (i.e. Loan ID, issue date, status, original amount, outstanding amount, vintage, etc.). There is one line for each loan issued.
2 - The second table, called "PaymentRegister" is a record of all the payments on each loan made by borrowers over a period of time.
There is a one-to-many relationship from the LoanTape to the PaymentRegister. Basically, the LoanTape is a snapshot of each loan at a particular point in time, and the PaymentRegister is a record of what happened to each loan between over a specified period of time.
In the LoanTape, I was able to create an LOD expression called VintageOriginationAmt, which summed up the original loan amounts so that I could get to a total loan amount for each "Vintage", or discreet pool of loans specified by issue date (in this case, I am making pools determined by the calendar quarter in which the loans were issued).
In the PaymentRegister, I am able to calculate the amount of each loan has been paid or charged-off (taken as a loss) in a given month after it was issued.
On the tab "Charge-Offs by Month" I am trying to calculate the PERCENTAGE of a given Total Vintage amount that was charged-off in a month. Basically, the middle chart is meant to be a running total of the top chart divided by the bottom chart. However, I can't get the bottom chart correct. I cannot do the calculation because the Total Vintage on the bottom chart amount is an LOD from the LoanTape, and the Charged-off amount is a sum from the PaymentRegister.
Tableau won't allow me to bring the LOD from one table to the other. As you can see, the bottom chart is using the total amount of loans originated over ALL of the vintages, not just the vintage selected, which is the one I want to use.
Then my goal is to build a chart that looks like the middle chart, but has a separate line for each vintage, and show them all on the same chart.
Is there a way to do this? I have looked around other forums, I see that some of found a workaround, but I can't seem to get anything working here.
I've tried it by joining the tables, but run into other problems. Though it seems there should be a good way to do this.
Thanks so much in advance.