6 Replies Latest reply on Dec 14, 2016 6:29 PM by Shinichiro Murakami

    Payment Schedule Logic

    Harnish Patel

      Hello all,

       

      I am trying to recreate logic for my payment schedule but strictly using a calculated field in Tableau.

       

      The framework is as follows:  All of my tickets have a start date. The start date marks the beginning of the payment and I pay 25% for the first 3 months, 50% for the following 3, 75% for the next 3, and 100% thereon. The schedule are different per year as well.

       

      I believe this would be a combination of IF THEN with some LOOKUP logic, but I am not very familiar with LOOKUP and would really appreciate some help on that end.

       

      All I want to do with this field is create the percentages, I will do the math with the $$ in another field.

       

      Thank you in advance for your help!!

       

      -HP

        • 1. Re: Payment Schedule Logic
          Nachiket Dekhane

          Hi Harnish,

           

          Could you please attach some mock up data? I am not sure what you want to achieve, but you can refer to below calculation that I came up with based on my understanding.

           

          IF DATEDIFF('month',[Start Date],TODAY()) <= 3 THEN "10%"

          ELSEIF DATEDIFF('month',[Start Date],TODAY()) > 3 AND DATEDIFF('month',[Order Date],TODAY()) <=6 THEN "50%"

          ELSEIF DATEDIFF('month',[Start Date],TODAY()) > 6 AND DATEDIFF('month',[Order Date],TODAY()) <=9 THEN "75%"

          ELSE "100%"

          END

           

          Hope this helps.

           

          Thanks,

          Nachiket

          • 2. Re: Payment Schedule Logic
            Shinichiro Murakami

            Definitely we need sample data as packaged workbook.

            I think you need LOD calc logic on top of Nachiket's solutions.

             

            Thanks,

            Shin

            • 3. Re: Payment Schedule Logic
              Harnish Patel

              Thank you both for your quick response,

               

              I've attached a workbook that is a start for what I am trying to accomplish

               

              Essentially what I would like is the following (the column headers in red are what I need to figure out) - Sheet: Ramp Up Schedule

               

              The logic is:

                   Start Date [1st Month] = 25%

                   Start Date [2nd Month] = 25%

                   Start Date [3rd Month] = 25%

                   Start Date [4th Month] = 50%

                   Start Date [5th Month] = 50%

                   Start Date [6th Month] = 50%

                   Start Date [7th Month] = 75%

                   Start Date [8th Month] = 75%

                   Start Date [9th Month] = 75%

                   Start Date [10+ Months] = 100%

               

              Eventually, in another sheet, the Amount will be multiplied to these percentages to give you a schedule of payments but for now I just want to lay out the table

               

              Thank you!

               

              0%

              DRGAmountStart DateAugust 2016September 2016October 2016November 2016December 2016January 2017February 2017March 2017April 2017May 2017June 2017July 2017August 2017September 2017...
              194421,4259/1/160%25%25%50%50%50%75%75%75%100%100%100%100%100%...
              301244,8932/1/170%0%0%0%0%0%25%25%25%50%50%50%75%75%...
              302225,6412/1/170%0%0%0%0%25%25%25%50%50%50%75%75%...
              • 4. Re: Payment Schedule Logic
                Shinichiro Murakami

                Until I understand your final goal, it's quite difficult to propose something.

                But at least at this point, you need to create this type of formated secondary data.

                 

                    

                DRG8/1/20169/1/201610/1/201611/1/201612/1/20161/1/20172/1/20173/1/20174/1/20175/1/20176/1/20177/1/20178/1/20179/1/2017
                72011111111111111
                69111111111111111
                66311111111111111
                66211111111111111
                66011111111111111
                56011111111111111
                54011111111111111
                51911111111111111
                51311111111111111
                46811111111111111
                46311111111111111
                46011111111111111
                42511111111111111
                42211111111111111
                38311111111111111
                36211111111111111
                35111111111111111
                34711111111111111
                32111111111111111
                31511111111111111
                31411111111111111
                31311111111111111
                31011111111111111
                30911111111111111
                30811111111111111
                30411111111111111
                30211111111111111
                30111111111111111

                 

                 

                Connect to this data and edit data source and pivot.

                // in Date Master

                [Date 2]

                date(datetrunc('month',date([Pivot Field Names])))

                 

                 

                // in original data

                [Date 2]

                date(datetrunc('month',[DRG Start]))

                 

                Because I align the field name and property, it's automatically connected.

                 

                Then you can put Data Master 's Date and DRG as dimensions..

                 

                 

                Thanks,

                Shin

                • 5. Re: Payment Schedule Logic
                  Harnish Patel

                  This is very close. Now all I want to display in this schedule those percentages that I mentioned above. So Depending on when the start date is, the first 3 months are "25%", the next 3 are "50%", the following 3 are "75%", and from then to the end of the schedule is 100% (Below)

                       Start Date [1st Month] = 25%

                       Start Date [2nd Month] = 25%

                       Start Date [3rd Month] = 25%

                       Start Date [4th Month] = 50%

                       Start Date [5th Month] = 50%

                       Start Date [6th Month] = 50%

                       Start Date [7th Month] = 75%

                       Start Date [8th Month] = 75%

                       Start Date [9th Month] = 75%

                       Start Date [10+ Months] = 100%

                  • 6. Re: Payment Schedule Logic
                    Shinichiro Murakami

                    You keep asking difficult questions

                     

                    Don't link "Date", only "DRG"

                    [Date First]

                    attr([Rz Schedule+ (Rz Schedule)].[Date 2])

                     

                    [Date Diff]

                    datediff('month', [Date First],attr([Date 2]))

                     

                    [Rate]

                    if [Date Diff] <0 then 0

                    elseif [Date Diff] <4 then 0.25

                    elseif [Date Diff]<7 then 0.5

                    elseif [Date Diff] <10 then 0.75

                    elseif [Date Diff] >=10 then 1

                    end

                     

                     

                     

                    Thanks,

                    Shin