I am looking for a set analysis that will allow me to show the sales each week for the past 13 weeks over a number of departments. I have used a basic set that provides the correct data (i.e. $=Only([Week])-1 $=Only([Week])-2 $=Only([Week])-3 etc).
However, when i want to see the 13 weeks data from say week 5 of 2010 i will correctly see the last 5 weeks but then no sales information for weeks 45,46...52 of 2009.
would I be correct in thinking some sort of "if" statement may be needed?
currently using Version 10.
data fields Include; [Volume] for sales, [Week], [Fiscal Year], [Invoice Date]
I would probably create a "Year Week" field defined as the first date in the week. You'll also want a "Week Year" field defined as the year the week began in. For instance, the last week of 2010 includes January 1 and 2 2011, at least if you're using ISO weeks. Yes, there may be less confusing names for these two fields. So the user selects a Week and a Week Year. Then you can get the 13 weeks like this (untested):
