Filtering data for last 8 weeks and next 8 weeks

[tonypreece]

I have a dataset measuring performance by week number (1-52) with actual and forecast volumes.

The actuals run upto last week then future dates have 0. Forecasts show for previous and future weeks.

I need a way to cut the data so i only have the previous eight weeks and next eight weeks, ready for use in an interactive chart for outputting.

For the current week I need to go from year 2019 week 52 to 2020 week 15. (7 now and 8 forward).

I've used summarise to get the max year and max week within that year, but I'm not struggling to pick out the last eight/next eight weeks. I'm thinking maybe a formula in the multi-row tool to look at Year and Week and return a true/false response that I can then filter on.

Based on [YearWeek] (e.g. 2020-7) I want to go back to 2019-52 and forward to 2020-15). My data is sorted by [product]s so I can't simply go eight rows down, if the forecast doesn't run that far ahead it needs to stop or will pull in historic data for the next [product].

Any ideas how to achieve this? 

[tonypreece · Accepted solution]

I went with a completely different approach and managed to solve it.

By filtering on [volume] > 0 then sorting by descending data, I could then use sample to pull out the correct number of rows.

I did the same with volume = 0 sorting in ascending date to sample 8 weeks forward and union the two together.