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 History Summary Calculations
© 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 concept of a thirteen month rolling history and provided methods for maintaining that structure. This section takes a look at how we can roll up our history in various ways.


Overview

If you are not familiar with the structure of our thirteen month rolling history, take some time and review it.

The array structures presented were:
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
Having the last thirteen months available allows considerable flexibility in calculating useful summaries, some of which are:
  1. All months of the current fiscal year
  2. All months of the current fiscal quarter
  3. All months of the previous fiscal quarter
  4. Same month last year

Common Routines

However we want to summarize our month totals, we need to have the ability to take one month's data and add it to our running tally.

cmSumOneMonth
Proc cmSumOneMonth(siMonth SmallInt,
                   var arHistory arHistoryMonths,
                   var arSummary arHistorySummary)
;
; Add the siMonth values from arHistory to arSummary
;
var
siIndex         SmallInt
siHistoryIndex  SmallInt
endVar
siHistoryIndex=cmHistoryLocation(siMonth,arSummary.size())
for siIndex from 1 to arSummary.size()
  arSummary[siIndex] = arSummary[siIndex] + arHistory[siHistoryIndex]
  siHistoryIndex = siHistoryIndex + 1
endFor
endProc
Before summarizing our monthly totals, we also need to prepare and initialize our summary array.

cmClearSummary
Proc cmClearSummary(var arHistory arHistoryMonths, var arSummary arHistorySummary)
;
; Calculate size of one months history
; and initialize summary totals
;
var
siIndex     SmallInt
endVar
arSummary.empty()
arSummary.grow(cmHistorySize(arHistory))
for siIndex from 1 to arSummary.size()
  arSummary[siIndex] = 0
endFor
endProc

Year To Date

A Year To Date (YTD) summary is the grand total of the current month and all previous months of the current fiscal year.

emYearToDate
method emYearToDate(var siHistoryYear SmallInt,
                    var siHistoryMonth SmallInt,
                    var arHistory arHistoryMonths,
                    var arSummary arHistorySummary,
                    siStartFiscalMonth SmallInt)
  ;
; Calculate Year To Date History Summary
;
var
siMonth     SmallInt
endVar
;
; Initialize summary totals
;
cmClearSummary(arHistory,arSummary)
;
; YTD Totals include the current month (13) and
; all other previous months of the same or fiscal
; year period
;
switch
;
; If history month is the first month of the fiscal year
; then no previous months of the same year are available
;
case siHistoryMonth = siStartFiscalMonth :
otherwise :
;
; Add in previous months of the same fiscal year
;
  siMonth = siStartFiscalMonth
  while siMonth <> siHistoryMonth
    cmSumOneMonth(siMonth,arHistory,arSummary)
    siMonth = iif(siMonth = 12,1,siMonth + 1)
  endWhile
endSwitch
;
; Add in current month
;
cmSumOneMonth(13,arHistory,arSummary)
endMethod

Quarter To Date

A Quarter To Date (QTD) summary is the grand total of the current month and all previous months of the current fiscal quarter.

emQuarterToDate
method emQuarterToDate(var siHistoryYear SmallInt,
                       var siHistoryMonth SmallInt,
                       var arHistory arHistoryMonths,
                       var arSummary arHistorySummary,
                       siStartFiscalMonth SmallInt)
;
; Calculate Quarter To Date History Summary
;
var
siMonth     SmallInt
endVar
;
; Initialize summary totals
;
cmClearSummary(arHistory,arSummary)
;
; QTD Totals include the current month (13) and
; all other previous months of the same or fiscal
; year quarter
;
; Calculate month (1-3) of the current quarter
;
siMonth = smallInt(cmAMod(3,emFiscalMonth(siHistoryMonth, siStartFiscalMonth)))
switch
;
; If history month is the first month of the fiscal quarter
; then no previous months of the same quarter are available
;
case siMonth = 1 :
otherwise :
;
; Add in previous months of the same fiscal quarter
;
  siMonth = siMonth - 1
  while siMonth > 0
    cmSumOneMonth(smallInt(cmAMod(12, siHistoryMonth - siMonth)), arHistory, arSummary)
    siMonth = siMonth - 1
  endWhile
endSwitch

;
; Add in current month
;
cmSumOneMonth(13,arHistory,arSummary)
endMethod

Last Quarter To Date

A Last Quarter To Date summary is the grand total of the three months prior to the first month of the current quarter.

emLastQuarter
method emLastQuarter(var siHistoryYear SmallInt,
                     var siHistoryMonth SmallInt,
                     var arHistory arHistoryMonths,
                     var arSummary arHistorySummary,
                     siStartFiscalMonth SmallInt)
;
; Calculate Last Quarter History Summary
;
var
siMonth         SmallInt
siQuarterMonth      SmallInt
endVar
;
; Initialize summary totals
;
cmClearSummary(arHistory,arSummary)
;
; Last Quarter Totals are the three months prior
; to the first month of the current quarter.
;
; Calculate month (1-3) of the current quarter
;
siQuarterMonth=smallInt(cmAMod(3, emFiscalMonth(siHistoryMonth, siStartFiscalMonth)))
;
; Calculate last month of previous quarter
;
siMonth=smallInt(cmAMod(12,siHistoryMonth-siQuarterMonth))
;
; Add in the three months of the last fiscal quarter
;
siQuarterMonth = 3
while siQuarterMonth > 0
  cmSumOneMonth(siMonth,arHistory,arSummary)
  siQuarterMonth = siQuarterMonth - 1
  siMonth = smallInt(cmAMod(12,siMonth - 1))
endWhile
endmethod

Same Month Last Year

Same Month Last Year is simply the values referenced by the history as of month.

emSameMonthLastYear
method emSameMonthLastYear(var siHistoryYear SmallInt,
                           var siHistoryMonth SmallInt,
                           var arHistory arHistoryMonths,
                           var arSummary arHistorySummary)
;
; Calculate Same Month Last Year History Summary
;
;
; Initialize summary totals
;
cmClearSummary(arHistory,arSummary)
;
; Same Month Last Year is equal to month
; identified by siHistoryMonth
;
cmSumOneMonth(siHistoryMonth,arHistory,arSummary)
endmethod

Conclusion

We now have methods that support the calculation of various summaries derived from thirteen month rolling histories. Additional types of summaries are possible and it is left to the reader to explore that area. You can use these methods as building blocks supporting additional calculations such as averages if fields such as quantity and cost are available.

A form and table demonstrating fiscal periods, thirteen month rolling histories and the above summary calculations are available here.


Next: I'm taking a break! I have other ideas and if you have some related to Paradox® Date and Time Types, drop me a line anytime.

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.