Payment Schedule

[Anonymous]

I have a table resembling a payment schedule:

ID	Start Date	End Date	Frequency	Cost
123	1/1/2018	12/31/2020	Monthly	50
456	2/1/2018	12/31/2020	Annually	300
789	3/1/2018	12/31/2020	Quarterly	120

I then created a Pivot chart where each column is a Year-Month (see attached) like below. Please see the Load script in the file.

ID	2018-Jan	2018-Feb	2018-Mar	2018-Apr	2018-May	2018-Jun	2018-Jul	2018-Aug	2018-Sep	2018-Oct	2018-Nov	2018-Dec	2019-Jan	2019-Feb	etc....
123	50	50	50	50	50	50	50	50	50	50	50	50	50	50	50
456	0	300	0	0	0	0	0	0	0	0	0	0	0	300	0
789	0	0	120	0	0	120	0	0	120	0	0	120	0	0	120

The above is the desired result. In my file, you will see where ID=456, the amount of 300 is repeating each month. Same case where ID=780, 120 is repeating each month. ID=123 is fine because the Frequency is "Monthly". I am so far unable to factor the [Frequency] into the particular months that the Costs should reflect on the pivot chart.

If the [Frequency] for a record is "Annually", I want the Cost to reflect on the 1st month, zeros the next 11 months, and then reflect the cost on the 13th month and so forth (i.e. first month every 12 months). If the [Frequency] for the record is "Quarterly", I want the Cost to reflect on the 1st month, zeros on the 2nd and 3rd month, and then reflect the cost on 4th month and so forth (i.e. first month every 3 months).

[sunny_talwar]

Try this

=If(Frequency = 'Annually',

If(SetDateYear([Start Date], Year(Today())) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())), Sum(MonthlyCost)),

If(Frequency = 'Quarterly',

If(SetDateYear([Start Date], Year(Today())) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())) or

  AddMonths(SetDateYear([Start Date], Year(Today())), 3) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())) or

  AddMonths(SetDateYear([Start Date], Year(Today())), 6) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())) or

  AddMonths(SetDateYear([Start Date], Year(Today())), 9) = SetDateYear(Date#(YearMonth, 'YYYY-MMM'), Year(Today())), Sum(MonthlyCost)),

If(Frequency = 'Monthly', Sum(MonthlyCost))))

[Anonymous]

Hi Sunny, this is amazing!

So I noticed that if the Start Date is in the middle of the month e.g. January 20, the record does not get pulled to the pivot table. What I added to your expression is put a MonthStart on the [Start Date]. This resolved pulling records with partial months. My challenge now is that where the [End Date] is in the middle of the month (i.e. End Date is not the last day of the month) is that the last month gets calculated with a cost, but I don't want it to do so.

So for example:

Start Date, End Date, Frequency, Cost

1/20/2018, 7/19/2018, Monthly, 500

Right now it will show 500 on 2018-Jan, 2018-Feb, 2018-Mar, 2018-Apr, 2018-May, 2018-Jun, 2018-Jul. However I only need it to calculate until 2018-Jun. In other words if [End Date] is not the last day of the month, the pivot table should not calculate for that month. I'm currently struggling how to implement this.

So in the above:

DEF should not reflect any cost for 2019-Jan. Last "payment" should be 2018-Oct.

GHI should not reflect any cost for 2018-Jul. Last "payment" should be 2018-Jun.

ABC should not reflect any cost for 2019-Jan. Last "payment" should be 2018-Jan.

[sunny_talwar · Accepted solution]

May be try changing Calendar script to this

Calendar:

load Distinct MonthStart(tempDate) as %DateKey,

Floor(MonthStart(tempDate)) as DateNum,

Year(tempDate)&'-'&Month(tempDate) as YearMonth,

Year(tempDate) as CostsYear,

1 as MonthMultiplicator

resident tempDates;

LOAD recID,

Monthstart(AddMonths([Start Date],iterno()-1)) as %DateKey,

Cost as MonthlyCost

Resident PAYMENTS

While Monthstart(AddMonths([Start Date],iterno()-1)) <= If(Floor(MonthEnd([End Date])) = [End Date], Monthstart([End Date]), Monthstart([End Date], -1));

[Anonymous]

AMAZING!

[Anonymous]

Hi Sunny,

Hoping can get your insights on this even if I've already closed it. I expanded the data so that a record can have two Expense Type (Expense A, Expense B) and also can have multiple Frequencies. If need me to open a new thread, let me know.

I have observed that the pivot chart does not want to calculate for records that have multiple Frequencies unless a selection is made on [Frequency] or [Expense Type]. Been trying to modify the Expression since forever but haven't been successful.

• ID=123 (has two rows, both are the same Expense Type and both are the same Frequency) - no issue. Pivot Chart correctly sums up the Costs.
• ID=456 (has two rows, different Expense Types and both are the same Frequency) - no issue. Pivot Chart correctly reflects the data for Expense A and Expense B.
• ID=678 (has two rows, both are the same Expense Type but one is Monthly and one is Quarterly) - does not pull to the chart unless user makes a Selection on [Frequency]. I am trying to make this work such that the cost of the two rows are being totaled. 2018-Jan would be 500 (300+200), 2018-Feb would be 300, 2018-Mar would be 300, 2018-April would be 500 (300+200)... etc. If user selects [Frequency], it partially works (see below, first image is if user selects "Monthly" and second image is if user selects "Quarterly"). The desired output is they should reflect at the same time (totaled) and without need for user selection.

• ID=789 (has two rows, different Expense Types and one is Quarterly and one is Monthly) - does not pull to the chart unless user makes a Selection on both [ID] and any [Expense Type]. See image below which is the output when I selected ID=789 and selected any Expense Type. It is correct however I am trying to get the Pivot Chart to reflect this without user selection.

         

Thanks!

[sunny_talwar]

Check now

For 678

For 789