We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Filtering data for last 8 weeks and next 8 weeks

10 - Fireball

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? 

10 - Fireball

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.