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

# Payment Schedule Logic

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.

-HP

• ###### 1. Re: Payment Schedule Logic

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

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

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

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.

 DRG 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016 1/1/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 720 1 1 1 1 1 1 1 1 1 1 1 1 1 1 691 1 1 1 1 1 1 1 1 1 1 1 1 1 1 663 1 1 1 1 1 1 1 1 1 1 1 1 1 1 662 1 1 1 1 1 1 1 1 1 1 1 1 1 1 660 1 1 1 1 1 1 1 1 1 1 1 1 1 1 560 1 1 1 1 1 1 1 1 1 1 1 1 1 1 540 1 1 1 1 1 1 1 1 1 1 1 1 1 1 519 1 1 1 1 1 1 1 1 1 1 1 1 1 1 513 1 1 1 1 1 1 1 1 1 1 1 1 1 1 468 1 1 1 1 1 1 1 1 1 1 1 1 1 1 463 1 1 1 1 1 1 1 1 1 1 1 1 1 1 460 1 1 1 1 1 1 1 1 1 1 1 1 1 1 425 1 1 1 1 1 1 1 1 1 1 1 1 1 1 422 1 1 1 1 1 1 1 1 1 1 1 1 1 1 383 1 1 1 1 1 1 1 1 1 1 1 1 1 1 362 1 1 1 1 1 1 1 1 1 1 1 1 1 1 351 1 1 1 1 1 1 1 1 1 1 1 1 1 1 347 1 1 1 1 1 1 1 1 1 1 1 1 1 1 321 1 1 1 1 1 1 1 1 1 1 1 1 1 1 315 1 1 1 1 1 1 1 1 1 1 1 1 1 1 314 1 1 1 1 1 1 1 1 1 1 1 1 1 1 313 1 1 1 1 1 1 1 1 1 1 1 1 1 1 310 1 1 1 1 1 1 1 1 1 1 1 1 1 1 309 1 1 1 1 1 1 1 1 1 1 1 1 1 1 308 1 1 1 1 1 1 1 1 1 1 1 1 1 1 304 1 1 1 1 1 1 1 1 1 1 1 1 1 1 302 1 1 1 1 1 1 1 1 1 1 1 1 1 1 301 1 1 1 1 1 1 1 1 1 1 1 1 1 1

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

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

[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