![]() |
![]() |
|
![]() |
Paradox® Date, Time, and DateTime Data Types Odds and Ends - Thirteen Month Rolling Histories © 2002 Rick Kelly www.crooit.com Download the library containing the routines used in this series. Preface A library of all OPAL methods presented is available here. In the previous section, we explored the definition of fiscal periods. This section will address the concept of a thirteen month rolling history and incorporate our previous definitions of fiscal periods. Overview Financial applications such as General Ledger and its supporting journals typically maintain transactions and summary balances for the current period or month and the previous twelve periods. Closing out a period involves the creation of new records for the next period and dropping the oldest period from active history; maintaining the total active periods at thirteen. This strategy supports changes to the current period and any of the twelve prior periods as well as summaries such as year and quarter to date which are useful in a wide variety of application systems. Record Structure To define our history structure we'll need fields for:
The fields January through December represent the preceding twelve months prior to HistoryMonth and HistoryYear. It is not necessary to directly identify the year associated with the months January through December. Later on, we will demonstrate how to calculate the year when we need it. CurrentMonth is the summary total as of HistoryMonth and HistoryYear. Commonly, each of the thirteen months may contain an equal number of separate fields such as amounts and quantities which the presented methods will support. Please note that no error checking is performed in the presented methods. Definitions With the goal of having complete independence from any table structures, our general approach will be to use Paradox® fixed and re-sizeable arrays. Arrays will be loaded from the desired table, passed to the appropriate method and, on return, used to update the table. Our array definitions are: Type ; ; History last 13 calendar months ; ; Layout: ; January (1) July (7) ; February (2) August (8) ; March (3) September (9) ; April (4) October (10) ; May (5) November (11) ; June (6) December (12) ; Current Month (13) ; ; The months may also contain multiple entries so that ; the final array size is and must be a multiple of 13 ; ; arHistoryMonths = Array[] Number ; ; History Update Transaction ; ; Array contains the transaction data to update ; one month on an associated history master ; arHistoryUpdate = Array[] Number ; ; History Summary ; ; Array contains the summary totals requested ; arHistorySummary = Array[] Number ; ; For months January thru December, the associated year ; arHistoryMonthsAndYears = Array[12] SmallInt endType Common Routines A basic requirement to working with our history array is locating the correct starting position to access for a given month. Since each month may contain multiple fields, we need to know how many fields each history summary month contains. Knowing our history array is arranged from January (Month 1) through December (Month 12) immediately followed by the current month (Month 13), we can simply multiply the month number by the number of fields and subsequently subtract the number of fields and add one. For history months with 2 fields per month: (3 * 2) - 2 + 1 = 5 (Beginning array index for March)cmHistoryLocation Proc cmHistoryLocation(siMonth SmallInt, siMonthSize SmallInt) SmallInt ; ; Calculate beginning history array for siMonth ; return (siMonth * siMonthSize) - siMonthSize + 1 endProcGiven a history array, we can calculate the implied number of fields per month as follows: cmHistorySize Proc cmHistorySize(var arHistory arHistoryMonths) SmallInt ; ; Calculate the size or number of fields of ; one month's history ; return smallInt(arHistory.size() / 13) endProcA key feature of our history array is rolling months. If we are given an update request that is flagged with a date after the as of month and year of our history array, we will shift the months automatically. Assume we have history as of June 2002. Our history array would be dated as follows:
We receive an update request flagged as July 2002. We need to replace June 2001 with the current month (June 2002), clear the current month and update the as of month and year to July 2002 before applying the update. The following method handles this "shifting". cmShiftHistory Proc cmShiftHistory(var siHistoryMonth SmallInt, var arHistory arHistoryMonths) ; ; Save and Clear Current History ; var siMonthSize SmallInt siMonthStart SmallInt siCurrentMonth SmallInt siIndex SmallInt endVar ; ; Calculate size of one month's history ; siMonthSize = cmHistorySize(arHistory) ; ; Calculate beginning array location for month ; siMonthStart = cmHistoryLocation(siHistoryMonth, siMonthSize) ; ; Calculate beginning array location for ; current month's history ; siCurrentMonth = cmHistoryLocation(13,siMonthSize) ; ; Move and Clear Current History ; for siIndex from siMonthStart to siMonthStart+siMonthSize-1 arHistory[siIndex] = arHistory[siCurrentMonth] arHistory[siCurrentMonth] = 0 siCurrentMonth = siCurrentMonth + 1 endFor endProcUpdating One Month's History With the assistance of our previous support routines, we are now ready to update a history array. Our basic approach is:
emUpdateHistory method emUpdateHistory(var siHistoryYear SmallInt, var siHistoryMonth SmallInt, var arHistory arHistoryMonths, siUpdateYear SmallInt, siUpdateMonth SmallInt, var arUpdate arHistoryUpdate) ; ; Update One Month's History ; ; ; History last 13 calendar months ; ; arHistory Layout: ; ; January (1) July (7) ; February (2) August (8) ; March (3) September (9) ; April (4) October (10) ; May (5) November (11) ; June (6) December (12) ; Current Month (13) ; ; The months may also contain multiple entries so that ; the final array size is and must be a multiple of 13 ; ; siHistoryYear and siHistoryMonth are the as of date for ; the history record ; ; siUpdateYear and siUpdateMonth are the date of the values ; to be applied ; var siMonth SmallInt liElaspedHistoryMonths LongInt liElaspedUpdateMonths LongInt liIndex LongInt siStartingIndex SmallInt siUpdateIndex SmallInt endVar switch ; ; Check for History - create if missing ; case arHistory.size() < 1 : arHistory.grow(arUpdate.size() * 13) for siMonth from 1 to arHistory.size() arHistory[siMonth] = 0 endFor endSwitch ; ; Convert Year/Month to elapsed months ; liElaspedHistoryMonths = (siHistoryYear * 12) - 12 + siHistoryMonth liElaspedUpdateMonths = (siUpdateYear * 12) - 12 + siUpdateMonth switch ; ; Check if Update Year/Month are within last 12 months ; If not, no update is possible ; case liElaspedHistoryMonths-liElaspedUpdateMonths > 12 : otherwise : switch ; ; Check if Update Year/Month are in the future ; and drop/clear months as needed ; case liElaspedHistoryMonths-liElaspedUpdateMonths<0 : for liIndex from liElaspedHistoryMonths to liElaspedUpdateMonths - 1 siMonth = smallInt(cmAMod(12,liIndex)) cmShiftHistory(siMonth,arHistory) endFor siHistoryMonth = siUpdateMonth siHistoryYear = siUpdateYear liElaspedHistoryMonths = liElaspedUpdateMonths endSwitch ; ; Calculate history array index ; siStartingIndex = cmHistoryLocation(iif(liElaspedHistoryMonths = liElaspedUpdateMonths, 13, siUpdateMonth), arUpdate.size()) ; ; Update History ; siUpdateIndex = 1 for liIndex from siStartingIndex to siStartingIndex + arUpdate.size() - 1 arHistory[liIndex] = arHistory[liIndex] + arUpdate[siUpdateIndex] siUpdateIndex = siUpdateIndex + 1 endFor endSwitch endMethodThere are times when presenting our history summary totals on forms and reports where we would like to label the months with the implied year. The following method returns an array of twelve entries with the implied fiscal year. emMonthsAndYears method emMonthsAndYears(var siHistoryYear SmallInt, var siHistoryMonth SmallInt, var arYears arHistoryMonthsAndYears, siStartFiscalMonth SmallInt) ; ; Calculate the associated year(s) for the history ; months January - December ; var siMonth SmallInt siYear SmallInt endVar ; ; Initialize year(s) array ; arYears.empty() for siMonth from 1 to 12 ; ; Calculate the calendar year ; siYear = iif(siMonth < siHistoryMonth, siHistoryYear,siHistoryYear - 1) ; ; Calculate the fiscal year ; arYears[siMonth] = emFiscalYear(date(siMonth,1,siYear), siStartFiscalMonth) endFor endMethod Conclusion We now have methods that support the creation and maintenance of thirteen month rolling histories. Next: Thirteen Month Rolling History Summaries - Year To Date, Quarter To Date, Last Quarter and more! References Common / Shared ObjectPAL® Routines Discussion of this article |
![]() Feedback | Paradox Day | Who Uses Paradox | I Use Paradox | Downloads ![]() |
|
![]() The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation. Paradox is a registered trademark of Corel Corporation. ![]() |
|
![]() Modified: 15 May 2003 Terms of Use / Legal Disclaimer ![]() |
![]() Copyright © 2001- 2003 Paradox Community. All rights reserved. Company and product names are trademarks or registered trademarks of their respective companies. Authors hold the copyrights to their own works. Please contact the author of any article for details. ![]() |
![]() |
|