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 P Please consider the environment before printing this e-mail
