a rolling six years of data in a table and have meaningful column field names and descriptions that change year-to-year?

[Eric Lehti]

a rolling six years of data in a table and have meaningful column field names and descriptions that change year-to-year?

How can this application be redesigned to dynamically store a rolling six years of data in a table and have meaningful column field names and descriptions that change year-to-year?

We have a custom IBM i table with 12 monthly columns for the year 2007, 12 fields for 2008, 12 for 2009, and 36 for 2010, 2011, and 2012.
Sales history (F42119) is summed and put into the appropriate month buckets.
The data is downloaded to a spreadsheet and imported to a Windows sales forecasting application which puts Forecast data is put into future year buckets. Meaningful field names are nice at this juncture.
We upload Future year data to our IBM i Sales Forecast File F3460.

In January 2012 I will need to delete the 12 fields for 2007 and add 12 fields for 2013 and modify the RPG program accordingly.

What redesign would allow us to dynamically store a rolling six years of data in a table and have meaningful column field names and descriptions that change dynamically from year-to-year? So that I would never need to modify the RPG program nor the file?

 

[EmmanuelW]

Re: a rolling six years of data in a table and have meaningful column field names and descriptions that change year-to-year?

There may be a better way, but how about simply making the file columns generic? So, the columns simply represent current-year, 1-year-ago, etc.
Your program knows the current year, so it can determine the values to assign to each column for reports or calculations.