Thanks, Gary.
This will give me a project (like I do not have enough now) for next year. It really should not be that difficult since we only go back the previous two years. I will just have someone do the data entry part once I get the table set up. James Belisle ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Gary Wendike Sent: Thursday, December 09, 2010 10:26 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Week of the Year Yes...that would be how I would define the weeks. Two things I can share. 1. Our numbering system for the weeks is YYYYWW. For week 1 of the year 2010 would be 201001. 2. I use a counter to provide a continuous numbering system. I our business, we sometimes want to look at the last 13 weeks. This may be over a time frame when you switch years. If you subtract 13 weeks from the week of 201105, you would only consider the first 5 weeks of 2011. Here is the definition I use for my date tracking table... 1 WMWeek Type : INTEGER NOT NULL WM Week 2 WkIndx Type : INTEGER NOT NULL Sequencial index for week identification 3 CompWk Type : INTEGER Comparative week for previous year. Helps when there is 53 wks 4 WMMonth Type : INTEGER WM Month - Their system is based on 4-5-4 for 13 weeks 5 WMFYr Type : TEXT 6 Their fisal year. Their fiscal year begins approx Feb 1 6 WMCYr Type : TEXT 6 The Calendar year. 7 WkBegin Type : DATE Beginning date of the week 8 WkEnd Type : DATE Ending date of the week 9 SPKWeek Type : TEXT 8 Our company's fiscal week I am sure there are other aspects you can come up with. I do find the WkIndx to be a valuable column to determine the timeframe between weeks. Gary ________________________________ From: Jim Belisle <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thu, December 9, 2010 6:55:08 PM Subject: [RBASE-L] - Re: Week of the Year Gary and Jan, I appreciate the input. I agree that consistency of the parameters is the key. I would say that what you do Gary , track the weeks as you go, is the best way to go. Would I be correct in thinking you define the weeks by dates as in: Wk start wk end week number 01/04/2009 01/11/2009 1 Etc? James Belisle ________________________________ From: [email protected] [mailto: [email protected] ] On Behalf Of jan johansen Sent: Thursday, December 09, 2010 6:45 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Week of the Year James, Sounded interesting. I do a Year over Year looking at months so I just modified for weeks. SET VAR vCurrentYear = (IYR4(.#DATE)) SET VAR vPreviousYear = (IYR4(ADDYR(.#DATE,-1))) SET ERROR MESSAGE 677 OFF DROP VIEW CurrentYearWeeklySummary DROP VIEW LastYearWeeklySummary SET ERROR MESSAGE 677 ON CREATE TEMPORARY VIEW `CurrentYearWeeklySummary` + (Week,TotalSales) + AS SELECT + 1,(SUM(InvoiceTotal)) FROM InvoiceHead + WHERE (IYR4(InvoiceDate))=.vCurrentYear AND (IWOY(InvoiceDate)) = 1 + UNION SELECT + 2,(SUM(InvoiceTotal)) FROM InvoiceHead + WHERE (IYR4(InvoiceDate))=.vCurrentYear AND (IWOY(InvoiceDate)) = 2 + UNION SELECT + 3,(SUM(InvoiceTotal)) FROM InvoiceHead + WHERE (IYR4(InvoiceDate))=.vCurrentYear AND (IWOY(InvoiceDate)) = 3 + UNION SELECT + 4,(SUM(InvoiceTotal)) FROM InvoiceHead + WHERE (IYR4(InvoiceDate))=.vCurrentYear AND (IWOY(InvoiceDate)) = 4 CREATE TEMPORARY VIEW `LastYearWeeklySummary` + (Week,TotalSales) + AS SELECT + 1,(SUM(InvoiceTotal)) FROM InvoiceHead + WHERE (IYR4(InvoiceDate))=.vPreviousYear AND (IWOY(InvoiceDate)) = 1 + UNION SELECT + 2,(SUM(InvoiceTotal)) FROM InvoiceHead + WHERE (IYR4(InvoiceDate))=.vPreviousYear AND (IWOY(InvoiceDate)) = 2 + UNION SELECT + 3,(SUM(InvoiceTotal)) FROM InvoiceHead + WHERE (IYR4(InvoiceDate))=.vPreviousYear AND (IWOY(InvoiceDate)) = 3 + UNION SELECT + 4,(SUM(InvoiceTotal)) FROM InvoiceHead + WHERE (IYR4(InvoiceDate))=.vPreviousYear AND (IWOY(InvoiceDate)) = 4 I only did this for four weeks so you should be able to get the gist of it. CAUTION!!! The views are created pretty fast but looking at them is slow. Another thing when I looked at it is that there are no sales in week1 for the current year. That's because the first day of the year was a Friday so the first full week begins on January 4, 2010. Jan -----Original Message----- From: "Jim Belisle" <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Thu, 9 Dec 2010 18:13:32 -0600 Subject: [RBASE-L] - Re: Week of the Year On the same line of questioning how do I go back to a specific week from a previous year. I find first of all this years week as you mentioned to Victor. I then want to go back to the same week of the previous year. But on top of that, I want to total the sales for all the days of the previous year on the same week. Right now I just go be same dates. I want the same week. James Belisle ________________________________ From: [email protected] [mailto: [email protected] ] On Behalf Of jan johansen Sent: Thursday, December 09, 2010 6:10 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Week of the Year SET VAR vIWOY = (IWOY(.#DATE)) -----Original Message----- From: "Victor Timmons" <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Thu, 9 Dec 2010 16:05:11 -0800 Subject: [RBASE-L] - Week of the Year Does anyone have a formula that can figure out what week of the year the current date is in. 12/09/2010 is in week 49 Victor Timmons Tiz's Door Sales, Inc Visit us at www.tizdoors.com <http://www.tizdoors.com/> P Please consider the environment before printing this e-mail
