
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Loop through records to add month end date
Hello,
I need to create a new table from an existing table. It needs to have a month end date for every month from the start of a record opening until it's closed. See below.
Table
Rec Number | Opened | Closed |
5000 | 1/1/2023 | 5/11/2023 |
New Table
Rec Number | Opened | Closed | MonthEnd |
5000 | 1/1/2023 | 1/31/2023 | |
5000 | 1/1/2023 | 2/23/2023 | |
5000 | 1/1/2023 | 3/31/2023 | |
5000 | 1/1/2023 | 4/30/2023 | |
5000 | 1/1/2023 | 5/11/2023 | 5/31/2023 |
The goal is to create a P50 and P90 Aging balance by month. This is the percentile value of the aging of the balance items. P90 would be 90% of the items have an age at or below the p90 value for a given month. Having the month end will allow me to calculate the days it was open as of the end of that month. There may be a better way to do this, so any recommendations would be appreciated.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
// Original Table
OriginalTable:
LOAD * INLINE [
RecNumber, Opened, Closed
5000, 1/1/2023, 5/11/2023
];
// New Table
NewTable:
LOAD
RecNumber,
Opened,
Closed,
MonthEnd
RESIDENT OriginalTable;
// Loop through months and generate MonthEnd values
FOR i = 0 TO DateDiff('M', Num(peek('Opened')), Num(peek('Closed')))
LET vMonthEnd = Date(MonthEnd(AddMonths(Num(peek('Opened')), i)), 'MM/DD/YYYY');
IF vMonthEnd > Num(peek('Closed')) THEN
EXIT FOR; // Exit loop if we go beyond the Closed date
ENDIF
NOCONCATENATE
LOAD
RecNumber,
Opened,
Closed,
'$(vMonthEnd)' AS MonthEnd
RESIDENT OriginalTable;
NEXT
DROP TABLE OriginalTable; // Drop the original table if not needed
