Compare to same day of week, previous year?

[bmurch71]

I'm writing a set of queries/reports to create sales projections for the coming year. I have a table with the sales data from the previous year by date, and I want to look at the coming year and project sales for any given day based on the sales the same day of the week last year. I.E. to project sales for January 1, 2005 (a Saturday) I would need to look at sales from January 3, 2004 (the comparable Saturday). It would also need to account for leap year. Any ideas on how to accomplish this? Is it a ridiculous question?
Thanks!
Brian

 

[tonnic]

Hi Brian,

at first you should make a calendar table with all necessary columns (year, month,date,weekday,week ....). Thats not very difficult and normally you have done it very fast with excel.

Based on this table you can make a tempory table with following columns:

date,weekday,week of the actual year, week of the last year. ly-week=actual week - 100 (200350=200450-100)

Now you should be able to compare the weekdays.

Attention: On the 1. January excel always starts with week 1, also when the 1.January is a Sunday and the week should normally be 52. Here you have to correct the weeks manually.

Let me know if you need more information.

 

[bmurch71]

Thanks for your reply. I'm not sure I understand how to format the initial table in excel based on your recommendation. Can you show me an example, perhaps? Also, how does the week (number) come into play once I want to do my day-to-day comparison? Thanks again for your help, I'm just a little muddled today!
Sincerely,
Brian

 

[tonnic]

Hi,

For example a calendar table like this (week starts with sunday - you can change this to monday)

year;month;week;date;weekday;dayofweek
2003;1;1;01.01.2003;4;wednesday
2003;1;1;02.01.2003;5;thursday
2003;1;1;03.01.2003;6;friday
2003;1;1;04.01.2003;7;saturday
2003;1;2;05.01.2003;1;sunday
2003;1;2;06.01.2003;2;monday
2003;1;2;07.01.2003;3;tuesday
2003;1;2;08.01.2003;4;wednesday
2003;1;2;09.01.2003;5;thursday
2003;1;2;10.01.2003;6;saturday
:
:
:
2004 12 53 26.12.2004 1 sunday
2004 12 53 27.12.2004 2 monday
2004 12 53 28.12.2004 3 tuesday
2004 12 53 29.12.2004 4 wednesday
2004 12 53 30.12.2004 5 thursday
2004 12 53 31.12.2004 6 friday
2005 1 1 01.01.2005 7 saturday
2005 1 2 02.01.2005 1 sunday
2005 1 2 03.01.2005 2 monday
2005 1 2 04.01.2005 3 tuesday


And now you can make a query where you compare something like that

.... WHERE a.year=(b.year-1) and a.week=b.week and a.dayofweek='monday' ...

Better is you create a tempory table with following columns:

dayofweek, date, week, week last year

and then you use this table more or less as a reference.

Tip: Look at the red record. Thats the problem, excel an also access are always starting a new week on the first january.

 

[mdusoe]

Maybe this will lead you down another road...

SELECT SalesDate, Data, IIf(Weekday([SalesDate]-364)=Weekday([SalesDate]),[SalesDate]-364,[SalesDate]-363) AS LastYearsDate
FROM tblTest;

The logic is that without a leap year involved, there are 364 days between the exact Weekdays of different years. So the weekday is the same as 364 days ago, then assume no leap year, and use that. If it is a different weekday, though, then the only other possibility is 363 days ago, so use that instead.

HTH,
Mike.

 

[bmurch71]

Thanks, tonnic and mike! Mike, your solution was ideal for what I needed - I appreciate your help!
Cheers,
Brian