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|
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:
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 :
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.
Solved! Go to Solution.
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.
But it should show as March starting with the year of the start date.
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.
Think I got it. I split the WHERE for "Annually" into two clauses (one for those starting in January and those that aren't):
where DateKey >= YearStart( DateKey) and DateKey <= YearStart( DateKey) and Frequency='Annually' and recID > 0 and Month([Start Date])='Jan';
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.
|Property Key||Start Date||End Date||Cost||Frequency|
Resulting table should be:
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?
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 & day from this date format
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: