Tweet
the problem i see is, how does the CL know what week to process? you would have to define current and previous year date range, i'd think. in the shops i worked in, that did this, we created a file with an entry for each week (as defined by the comptroller) and an entry for product and sales. In the CL we would point to the member by year and (year - 1) to run the forecast/comparison/etc. something like this: week 1-53 (53 was year end totals) item item number, of course sales sales in dollars and cents qty_oh qty on hand qty_sld qty sold each year had this file. we could extrapolate the data by reading through every record for both years, but by having this summary file, we could quickly generate the report(s), and create ad-hoc reports with query or SQL. -bret
-
-
Date Calc in CL
In my experience, sales comparisons were done with fiscal calendar, with a a 53 week fiscal year every six years in retail. The calcs I did were based on the package fiscal calendar file. I can't recall doing anything like that without being based on a fiscal calendar. rd -
Date Calc in CL
Ed Jaroll wrote: > I have a date range: 11/12/2006 - 11/18/2006. I want to compare this > period with last year's period which is 11/13/2005 - 11/19/2005. My > question is: Is there a fool-proof method for calculating last year's > dates from this year's prompted dates? Ed, I ran into a number of problems concerning date comparisons when writing some sales reports. Week numbers can be troublesome: compare the date of week 3 in 2000 to week 3 in 2001 (15th versus the 20th). For us, we ran into large discrepancies if we tried just straight MMDD to MMDD all year long; this was because some customers would push large orders through the last couple of days of the period. This same date could end up being in the next period the following year making it look like we had a big pick up the current year, but then everything drops down to equivalent the next week. The logic we agreed on was this: MMDD to MMDD values equivalent from one year to the next -except- when the ending date was a period end date - then grab the period start and end dates for the prior year's dates for comparison. This way we don't worry about week numbers being problematic and year over year month end numbers stay trued up. BillComment
-
Date Calc in CL
guess that makes sense also. although i am pretty certain that our week 53 was the totals...maybe there was a "week 54" that i dont recall. your reason, ralph, is what i remember our comptroller talking about. the "every six years" was a key phrase in his explanation. -bretComment
-
Date Calc in CL
yeah, it's the retail fiscal year standard, is my understanding, bret. While retail packages like JDA (which we had an older version of at a large retail operation I worked for) are 53 week compliant, the retail chain had a complex custom sales reporting and analysis system that wasn't. And the first 53 week year coming up when they hired me. That was my first project there. Lots of pressure, as publicly announced year to year sales comparisons are done based on that. The alternative spreadsheet hell for generating the numbers would not have been pretty. But getting back to this thread, without a fiscal calendar it sounds like the same week last year as this year comparison, which of course can cross calendar months and quarters. But if that's what is wanted here, I'm thinking get julian days of starting and ending dates, add one or two to each based on leap year, and get last year's date range from the beginning and ending julians, which will be the same relative week last year based on starting the same day of the week. But maybe some partitioning of the month regardless of day of week is desired. rdComment
-
Date Calc in CL
Thanks, Bill, Bret & RD for the input! I need to make this as dynamic as possible. The user will type a "todate" and and 4 "fromdates" one for a single day comparison, one for wkly, mthly, and qtrly. The todate will always be a Sat and the fromdates will always be a Sun. The single date could be any one from Sun thru Sat. For the current year, all dates are easy because they are input by the user. The prior year comparison dates are what I have to calculate based on the input dates. Our old method for doing this was that the user typed a single cur yr date range AND the prior year date range. Then the CL would use OPNQRYF to get the required data. Reckon they're getting too lazy for that. Also, I am trying to make the process run faster, and we all know OPNQRYf ain't the way to go! Ergo, I am tasked with giving them all the possiblities they would want in one fell swoop. I was hoping someone might know of a builtin function or tool to make the calcs easier. But I think I will just have to do it the hard way: one condition at a time.Comment
-
Date Calc in CL
Ed Jarroll wrote: Also, I am trying to make the process run faster, and we all know OPNQRYf ain't the way to go! There are many things that may be done to speed OPNQRYF. Among them, specifying ALWCPYDTA(*OPTIMIZE) will enhance the subsequent reads, while OPTIMIZE(*FIRSTIO) greases the OPNQRYF execution. DaveComment
-
Date Calc in CL
I was hoping someone might know of a builtin function or tool to make the calcs easier. Conversion to and from calendar date and julian date is built-in. Beyond that there is nothing being done that is standard to have built-in functions. When I added a similar computation to a sales analysis system with hundreds of programs with over a million lines of code, I wrote a standalone *INLR off program to call, and replaced every year to year date comparison calculation (sometimes in the form of YY-1, for example) with a call to the program to calculate date to compare to. It was a complex calculation that did file I/O and handled multiple input and output formats, so while that's a bit much for your needs, a subprocedure calculating previous year would do. For example, you should be able to determine other period starting dates like month and quarter from the week starting date. The input dates would have to be validated anyway. But with the function program I wrote, it handled 52 or 53 week year comparison reporting, and as close to six years have gone by, will be handling it again when the time comes. rdComment
-
Date Calc in CL
would still think about creating a file with the entries for year/week/item/qty/dollars/etc. run this once to get the past year(s) and then each period to get the weeks. we had a file that was keyed by year that contained year/being date/enddate that gave us the periods. a program ran against the item master to load the year/week/item and then a program ran each night to update the qty for the current fiscal year & week. 2006/01/widget/75/$220.00 once we had this, we could run reports based on 2005/wk# vs 2006/wk#. it really sped things up, using the summary file instead of OPNQRYF each time. if you want, you can make each year a separate file or a new member in the file, and use CL to override the two years to compare. This also was a way for us to purge our records now and then, and still have many years of sales data to compare. -bretComment
-
Date Calc in CL
Not trying to resolve the issue as I don't know retail industry... Just sharing my experience in trying to do similar code. I had the users give me just one date, converted it to julian, and then compared numbers for 7 days stating that day in each year... worked out ok for us.Comment
-
Date Calc in CL
In retail especially, it's important to compare same day of the week year to year, such as Saturday to Saturday. rdComment
-
Date Calc in CL
Not trying to resolve the issue as I don't know retail industry... Just sharing my experience in trying to do similar code. I had the users give me just one date, converted it to julian, and then compared numbers for 7 days stating that day in each year... worked out ok for us.Comment
-
Date Calc in CL
So Sunday to Saturday one year lumped together is compared to Monday to Sunday lumped together the next year. What is "ok for us"? Any business should be better served than that. rdComment
-
Date Calc in CL
I have a date range: 11/12/2006 - 11/18/2006. I want to compare this period with last year's period which is 11/13/2005 - 11/19/2005. My question is: Is there a fool-proof method for calculating last year's dates from this year's prompted dates? The purpose is to compare sales in the 3rd wk of Nov of this year with the 3rd wk of Nov of last year (as an example). I will take the prompted dates and the calculated dates and pass them to an RPG pgm to generate the required rpt. Thanks for any assist.Comment
-
Date Calc in CL
ralph is correct. if you dont compare day of week to day of week, then you will get skewed data. take a beer joint for instance: if day 1 of prior year = saturday and day 1 of current year = sunday your sales by day would show a great disparity. maybe not a really good example, but it's friday and i was thinking of beer, anyway.... -bretComment
Comment