Dynamic filter for Latest X weeks, based on latest common week

[nstoycheff]

Hi All, I'm new to Alteryx.  So far so good, but I'm struggling to create this filter.

I have weekly data coming in for different customers, at different times.  I want to query the information for the Latest X weeks, based on the latest common week.

ie: customer 1 is at week 100, customer 2 at week 100, customer 3 at week 99.

I want to query the latest 13 weeks, based on the latest common week, 99.

Any suggestions as to how to accomplish it?

So far I've set up a summarize tool to get the MAX week for each customer.  I then did another summarize tool to find the MIN out of those weeks, resulting in finding my latest common week of 99.  Now how would I apply the Latest X to that number?  I added a formula tool to calculate what that week would be, but I'm still confused as to how I can tell my query to only use weeks BETWEEN min and max.

Thanks

[danrh]

A couple different ways to tackle this:

- If you've calculated both the max and the min dates into a single record, append these fields back to your original data stream and then add a filter.

- Using the max week, use a Generate Rows to go back a number of weeks, then join on that week field.  Something like this:

[nstoycheff]

---

@danrhwrote:

A couple different ways to tackle this:

- If you've calculated both the max and the min dates into a single record, append these fields back to your original data stream and then add a filter.

- Using the max week, use a Generate Rows to go back a number of weeks, then join on that week field.  Something like this:

---

Thanks for your reply. 

I should have  mentioned I'm using InDB tools.  I don't see an option for generate rows, or appending back.  I think this link might be what I'm looking for.

[derekbelyea]

Similar solution

[danrh]

Yeah, In-DB tools are a bit limited.  The work-around provided in that link should work well.  In your summarize make sure you have a single record, create a dummy field in both that data stream and your original, and join the two.  At this point, filter to weeks that are between your min and max and you should have it.

Let me know if you need help with a mock-up.

[nstoycheff · Accepted solution]

Okay I think I get it.  I'm running the query as I type this..hopefully I did it right

#1 is my main join to create the data set I'm 'playing' in.

#2 is where I created a fake integer to join on later.  

#3 again I'm creating a fake integer to join on

#4 Joining on #2 and #3 integers.

#5 Filtering twice: once on weeks greater than, and once on week smaller than

[danrh]

Looks good to me.  Nice work!

[nstoycheff]

Yeah it worked, thanks for your help :)