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

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

Best Practices

In Database

In-Database

Accepted answers

nstoycheff

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

All comments

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:

solution.yxmd

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

Dynamic Filter.yxmd

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

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

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235