is WORK_WEEK a number going from 1 to 52? If so then I think your chart will only be guaranteed to work safely when 1 year is selected.
If you want to see continous weeks spanning year sthan you should add a continuos WORk_WEEK with values something like AAAA_1 thru AAAA_52 where AAAA is the year.
thanks for your reply pat,
yes WORK_WEEK is a number going from 1 to 52, so for 2011 there's a set of 1 to 52 and another set for 2010 and so on...
my calendar table has a YEAR column too, can i work out something from there like what you're suggesting?
anyway here are some of the screenshots to better illustrate my situation,
when a single year is selected:
2 examples showing multiple years selection:
I wasn't sure what your desired result was. If you want to see weekly results over several years, so that weeks 50,51 and 52 of 2010 are fllowed by weeks 1, 2 and 3 of 2011. Or if you wanted to see weeks 1, 2 and 3 always in the same column.
From the snaphsots posted it would seem that this second solution is what you want.
One solution to look at is teh use of the keyword NODISTINCT in your aggr() function. By default aggr() is expecting one value to come back to it from your dimension START_DATETIME but as you in fact have several years this will not be the case. NODISTINCT should cover this possibility.
the keyword is to be placed dircetly after the aggr( part of your code before your expressions.
The other possible solution woudl be to add YEAR as another dimension tou youarr aggr() statement. But start with NODISTINCT and see if that works for you.