Search the Community
Showing results for tags 'correlated query'.
my focus is across a whole 3 years (156 weeks) of data, beginning January 1st 2011 (or nearest date) and ending December 31st 2013 (or nearest date) – so while we are looking at 3 years total, we will only end up with 52 weeks in our clean version of the data, labelled Week 1 to Week 52 (and not dates).there will be items in this list that launched before 1st Jan 2011, for which part of the launch year will show up in the period we’re focused on. In order that data percentiles (will be calculated at later stage) are not clouded by this, we also need to move any part year data from launches preceding 1st Jan 2011 to the relevant column. To illustrate by example, an item launched in July 2010 will have 26 weeks in 2010 (that fall outside of our 3 yr window) and 26 weeks that fall inside our 3yr window, but the first week we see is actually week 27 of that item’s launch, and not week 1. So the 26 weeks of data that fall into our window, should be shifted out to align with week 27-52 of the ‘clean’ data columns – does this make sense? So i want to have another column next to the WeekNumber column, which will give me the correct number (from 1 to 52) to place the sales value for each product. If then you do a pivot as shown in http://i.imgur.com/VZVIlPb.jpg by arranging the weeknumber column in the Pivots Column area and Sales Value in Pivots Value area, you will see the sales values are then shifted properly to fit the 52 columns. So for each Product, 1] if the sales data starts from 1st week (W 2011 01) and its total weeks count is < 52, then it means some weeks sales data lying in previous year. So this data needs to be numbered to correct week (between 1 to 52), by finding its column: (52 - count of weeks containing data for that product + 1).2] if the sales data does not start from 1st week (W 2011 01) and its total week count is < 52 then move it to 1st week (W 2011 01) column in 1 to 52 weeks i.e. number it as 1, 2, .....<=52.3] if the data starts from 1st week and is 52 weeks data then keep it as it is showing from 1 to 52 weeks. i.e. number it as 1,2,....52. Hope this makes sense. see the linkback for further clarity. http://stackoverflow.com/questions/23825905/shifting-156-weeks-sales-figures-to-52-weeks