Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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));
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))))
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.
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));
AMAZING!
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.
Thanks!
Check now
For 678
For 789