Jan,
Those situations are cared for in our situation. I map out the next year's weeks based on the number of business days in the week at the year's start. If a week has three or more business days, that will be our first week of the year. The ones with two or less go onto the last week of the previous year. Since sales are very slow due to Christmas and New Years, sales are not affected much. For our purposes, this works out fine. This coming year is easy since the year ends on a Friday. James Belisle We also use the same criteria for the number of weeks in a month, since we do monthly sales as well. I appreciate any comments because my aging grey matter needs prodding. ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of jan johansen Sent: Friday, December 10, 2010 9:41 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Week of the Year James, Just a couple of clarifications. The reason my view did not show any sales for the first week of 2010 was that there was no activity. Jan 1-3 was a Friday thru Sunday so week 1 had no billing activity. Slow is in the eye of the beholder. Either of these views only took about 2 seconds to load to the screen on my single user laptop. Jan -----Original Message----- From: "jan johansen" <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Thu, 09 Dec 2010 16:44:50 -0800 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
