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

    Vintage curve Analysis in Tableau

    Lihos G
    Lihos G Nov 13, 2017 11:37 PM

      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?

      • 415 Views
      • Categories: Actions & Filters, Calculations, Data Sources & Connections, Dates & Times, Extracts, Formatting & Display, Queries & Performance, Views & Dashboards, Tableau Online, Tableau Public
      • Tags:
        • 1. Re: Vintage curve Analysis in Tableau
          Norbert Maijoor
          Norbert Maijoor Ambassador Nov 10, 2017 4:34 AM (in response to Lihos G)

          Hi Lihos,

           

          Could you share a workbook in .tbwx format?

           

          Regards,

          Norbert

          • 2. Re: Vintage curve Analysis in Tableau
            Lihos G
            Lihos G Nov 13, 2017 10:52 PM (in response to Norbert Maijoor)

            I have attached my workbook to the original post.

            Thanks

            • 3. Re: Vintage curve Analysis in Tableau
              Norbert Maijoor
              Norbert Maijoor Ambassador Nov 14, 2017 2:11 AM (in response to Lihos G)

              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
                Lihos G Nov 20, 2017 11:21 PM (in response to Norbert Maijoor)

                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.