4 Replies Latest reply on Nov 20, 2017 11:21 PM by Lihos G

    Vintage curve Analysis in Tableau

    Lihos G

      I am currently doing this in excel. I am finding it difficult to replicate in Tableau.

        this how the data looks.

       

        1st Column is the Booking Month (vintage). 2nd Column is the Amount to be used as denominator.

        Starting from Column 1 to N. Are Subsequent Months (use to track loan age).

        So for Apr-14 'Column C' means 1 month of that loan.

        I have converted the Date column into Fiscal year starting from April to March.

       

      Before calculating the Sum. We have to consider only those columns which has all the values (no blanks). Yellow colored values are to be considered.

      For Example: for FY 2014 we will use column 1 to 42, since Data is up to Sept 2017(FY2018) so for FY2014 (March is the last month for each financial year) March of FY2014 to Sept FY2018 is 42 months. Similarly for FY2015 : March of FY2015 to Sept of FY2018 is 30 months, so for FY2015 consider columns from 1 to 30. So on and so forth..

      In Other words, For Each Financial Year, we have to select that Month Column, where all the Loans that originated in a particular Financial Year has Values ignore Rest.

       

      I have attached my workbook. All the Columns Numbered 1,2,3...N has to be divided by 'Disbursal Month'.

      Below are The Excel Screenshots of the Process.

       

       

      How to do this in tableau?

        • 1. Re: Vintage curve Analysis in Tableau
          Norbert Maijoor

          Hi Lihos,

           

          Could you share a workbook in .tbwx format?

           

          Regards,

          Norbert

          • 2. Re: Vintage curve Analysis in Tableau
            Lihos G

            I have attached my workbook to the original post.

            Thanks

            • 3. Re: Vintage curve Analysis in Tableau
              Norbert Maijoor

              Hi Lihos,

               

              Upfront it'' not final but an initial setup;) Find my approach as reference and stored in attached workbook version 10.3 located in the original thread.

               

               

               

               

              1. Pivoted the datasource

               

               

              1. D1. Monthnotation: mid([Month],1,3)

               

              2. D2. Month convert:

              if [D1. Monthnotation]="JAN" then 1

              elseif [D1. Monthnotation]="FEB" then 2

              elseif [D1. Monthnotation]="MAR" then 3

              elseif [D1. Monthnotation]="APR" then 4

              elseif [D1. Monthnotation]="MAY" then 5

              elseif [D1. Monthnotation]="JUN" then 6

              elseif [D1. Monthnotation]="JUL" then 7

              elseif [D1. Monthnotation]="AUG" then 8

              elseif [D1. Monthnotation]="SEP" then 9

              elseif [D1. Monthnotation]="OCT" then 10

              elseif [D1. Monthnotation]="NOV" then 11

              elseif [D1. Monthnotation]="DEC" then 12

              END //PS did could be done more efficiently;)

               

              3. D3 Year: int("20"+str(mid([Month],5,2)))

               

              4. D4. Date: MAKEDATE([D3. Year],[D2. Month convert],1)

               

               

              5. D5. Dateadd: dateadd('year',1,[D4. Date])

               

              6. Drag required objects to the indicated locations.

               

               

              Please validate and look forward to receive your feedback.

               

              Regards,

              Norbert

              • 4. Re: Vintage curve Analysis in Tableau
                Lihos G

                Hey Nobert,

                It works like a charm. Thanks for helping me out.

                Though the  Calculated field 'M1. Divided by Disbursal Month' didn't gave the Correct Values.

                so I created three different Calculated fields 'sumOfDisbursalAmt' , 'sumPivotFields' and 'VintagePercentage' which did the trick.

                Now only concern is, How can I limit each row to a particular month(logic of which I explained in the Original Post).

                I am writing it here Again: We have to consider only those columns which has all the values (no blanks). Yellow colored values are to be considered.

                For Example: for FY 2014 we will use column 1 to 42, since Data is up to Sept 2017(FY2018) so for FY2014 (March is the last month for each financial year) March of FY2014 to Sept FY2018 is 42 months. Similarly for FY2015 : March of FY2015 to Sept of FY2018 is 30 months, so for FY2015 consider columns from 1 to 30. So on and so forth..

                In Other words, For Each Financial Year, we have to select that Month Column, where all the Loans that originated in a particular Financial Year has Values ignore Rest.