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:
month;week; sales actual year; sales last year
I know this is a tricky one. I hope someone can help me.