Load Script - break apart a schedule into individual months

[Anonymous]

I am trying to replicate a chart expression onto the Load Script so that the generated field, PeriodCost, can be utilized further.

I have imported data like such:

Property Key	Start Date	End Date	Frequency	Cost
A001	4/1/2018	9/30/2018	Quarterly	200
B001	1/15/2018	1/14/2019	Annually	1000
C001	1/15/2018	1/14/2019	Quarterly	120
D001	1/1/2018	6/30/2018	Monthly	500

It represents sort of a payment schedule.  For example, for A001, it has a payment of 200 on a quarterly basis (every 3 months) starting 4/1/2018 (until 9/30/2019) i.e. 6 quarterly payments consisting of 4/1/2018, 7/1/2018, 10/1/2018, 1/1/2019, 4/1/2019, 7/1/2019. For D001, it has a payment of 500 every month starting 1/1/2018 (until 6/30/2018) i.e. 6 monthly payments consisting of 1/1/2018, 2/1/2018, 3/1/2018, 4/1/2018, 5/1/2018, 6/1/2018.

Using the Load Script, I am trying to create a table where the payments schedule is broken down by month (e.g. 2018-Jan, 2018-Feb, etc.) showing the specific instance of each payment. With some help from this community a while back, I am able to do this as an expression in a chart on my dashboard but this time I would like to do this within the Load Script so that this generated table can be used further (not just in the chart).

The table I am able to create (see "OUTPUT" on the attached) was initially suitable except that it does not factor the [Frequency] field. You will see on the attached QVW file on the table called "OUTPUT" that every cost is repeated every month from Start Date to End Date regardless of [Frequency]. I am seeking to modify the script so that Frequency is factored in. For example, for A001, since the schedule refers to a  “Quarterly” (i.e. once every 3 months) frequency, it should show on the created table as:

Property Key	Year-Month	PeriodCost
A001	2018-Apr	200
A001	2018-Jul	200
A001	2018-Oct	200
A001	2019-Jan	200
A001	2019-Apr	200
A001	2019-Jul	200

Currently the resulting table is showing a value of 200 for every month from 4/1/2018 to 9/30/ 2019, which should not be the case as I need it to reflect the actual instances based on the Frequency.

For B001, the Frequency is “Annually” (i.e. once every 12 months) hence it should show on the resulting table as :

Property Key	Year-Month	PeriodCost
B001	2018-Jan	1000

Currently the resulting table is showing a value of 1000 for every month from 1/15/2018 to 1/14/2019, which should not be the case as I need it to reflect the actual instances based on the Frequency.

[Siva_Sankar · Accepted solution]

Hi Mark Ramiro,

Solution attached

[Anonymous]

Thank you. This is really really helpful. I encountered one little bit. I noticed that if I happen to have in my data a record that has dates which are in the middle of the year, the output is not pulling the desired dates when Frequency is "Annually".

For example, please see for PropertyKey=E001 which I added on the revised qvw file. It has a Start Date of 3/15/2018 (March 15, 2018) and an End Date of 3/14/2021 (March 14, 2021) i.e. it would consist of 3 payments, 2018-Mar, 2019-Mar, and 2020-Mar.

In the current output it starts on January of the following year.

Property Key	DateKey	PeriodCost	YearMonth
E001	1/1/2019	888	2019-Jan
E001	1/1/2020	888	2020-Jan
E001	1/1/2021	888	2021-Jan

But it should show as March starting with the year of the start date.

Property Key	DateKey	PeriodCost	YearMonth
E001	3/1/2018	888	2018-Mar
E001	3/1/2019	888	2019-Mar
E001	3/1/2020	888	2020-Mar

So essentially the Month of the Start Date for a row with "Annually" Frequency should be the month start, whether it is January or March or any other month.

I've been trying to leverage the first_month_of_year portion of the Yearstart function in the Where clause but I'm not getting the desired results.

[Anonymous]

Think I got it. I split the WHERE for "Annually" into two clauses (one for those starting in January and those that aren't):

Load *

Resident PAYMENTS

where  DateKey >= YearStart( DateKey) and DateKey <= YearStart( DateKey)  and Frequency='Annually'  and recID > 0 and Month([Start Date])='Jan';

Load *

Resident PAYMENTS

where  DateKey >= YearStart( DateKey,0,num(month([Start Date]),'0')) and DateKey <= YearStart( DateKey,0,num(month([Start Date]),'0'))  and Frequency='Annually'  and recID > 0 and Month([Start Date])<>'Jan';

Follow- up question: Is there a way to handle half-year/semi-annual frequencies? I understand there's MonthStart, QuarterStart, YearStart functions to utilize but no I don't think I've seen a "HalfYearStart" (i.e. every 6 months) or similarly sounding.

For example:

Property Key	Start Date	End Date	Cost	Frequency
E020	7/1/2018	6/30/2020	1000	Semi-Annual

Resulting table should be:

Property Key	DateKey	PeriodCost	YearMonth
E020	7/1/2018	1000	2018-July
E020	1/1/2019	1000	2019-Jan
E020	7/1/2019	1000	2019-July
E020	1/1/2020	1000	2020-Jan

I am guessing I'd have to leverage YearStart for this and have it somewhat go every 6 months instead of the full year. Any thoughts?

[Siva_Sankar]

Hi Mark,

As far as i know there is no predefined function for half year start, but you can calculate something mentioned here how do i generate year, Half yr, quarter, weekly &amp; day from this date format

-Thanks.

Siva

[Anonymous]

I used the same methodology with what I did for Annual but did two sets and got it to work. The first set is basically exactly same as Annual. The second set of code is essentially the same except I moved it 6 months:

YearStart( DateKey,0,6)