Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Programming Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


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:
  1. As Of Year and Month
  2. January through December totals
  3. Field(s) representing the As Of Year and Month totals
  4. Application specific identifiers
A typical record might look like this (without any application specific identifier fields):

HistoryID      LongInt (primary key)
HistoryMonth      SmallInt
HistoryYear      SmallInt
CurrentMonth      Number
January      Number
...      ...
December      Number

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)
(12 * 2) - 2 + 1 = 23 (Beginning array index for December)
cmHistoryLocation
Proc cmHistoryLocation(siMonth SmallInt, siMonthSize SmallInt) SmallInt
;
; Calculate beginning history array for siMonth
;
return (siMonth * siMonthSize) - siMonthSize + 1
endProc
Given 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)
endProc
A 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:

January 2002      July 2001
February 2002      August 2001
March 2002      September 2001
April 2002      October 2001
May 2002      November 2001
June 2001      December 2001

Current Month = June 2002

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
endProc
Updating 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:
  1. If the update month and year is greater than twelve months ago, no update is possible
  2. If the update month and year are in future, save and clear months
  3. Determine the starting history array index
  4. Add the update amounts to the appropriate month
One benefit to this approach is that update requests may be handled in any order. Our history array, in saving and clearing monthly totals extends the time period as needed and updates the correct monthly total anywhere within the last thirteen months.

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
endMethod
There 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.