Skip to Content
0
Nov 30, 2005 at 09:12 AM

Problem with query

17 Views

Hello all.

I need to do a query which shows me sales by month and week compared between actual year and last year. To do it I have created two new characteristics one for number of week (1- 53) and one for month,the month is not exactly the same as 0calmonth2 because a week is always asociated to only 1 month and it will be the one with more days on it; example: week 5 of 2005 (31/01/2005 to 6/02/2005) will be set to month 2, because it has more days in febreary than in januuary.

Now thr problem is that wen I set the query, it may happend that week 5 in 2004 in is month 1 and week 5 of 2005 is in month 2, so I have two lines where I should have only 1. I tried setting the month as constant selection for "last year" and it works fine, but the problem is that I only get the weeks which have data for "actual year", where I should get the whole "last year" and those of "actual year" which have data.

here is an example:

data:

year;month;week;sales

2005;01;05;10

2005;02;06;13

2004;02;05;5

2004;02;06;7

query:

month;week; sales actual year; sales last year

01;05;10;5

02;06;13;7

I know this is a tricky one. I hope someone can help me.

Regards,

Javier.