Filter only for last 3 weeks

[sanastasova]

Hello, 

I want to create a formula that filters data from column only for the last three weeks. For example we are week 15, I want 15,14and 13. If we are week 16 I would want 16,15,14. How does Alteryx recognizes which is the current week for this column?

Here is a screenshot of what the column looks like.

Thanks for the help.

[RolandSchubert]

Hi @sanastasova ,

to identify the current week, you need to DateTime functions:
- DateTimeToday find the current day
- DateTimeFormat returns the week number

But: In addition it may be necessary to adjust the week number, because the number returned starts at "0". If the current year starts with a day between Monday - Thursday, the first week "counts" for the year, you have to add 1.

After finding the current week, a Filter tool does the week selection.

Let me know if it works for you.

Best,

Roland

[DavidSkaife]

Hi @sanastasova 

You should be able to use DateTimeFormat([date/time], "%U") which will return the week number as 00 – 53, with the beginning of weeks as Sunday. Alternatively you can use "%u" which starts the week on a Monday

[sanastasova]

Hi Roland, 

Is there a way I can just create a dropdown filter to choose which week I want. Like for example before I run the workflow just to select the 3 weeks I need. I think it would be more simple that way. 

Thanks!

Best, 

Simona

[danilang]

hi @sanastasova 

Here's a simple example using a Numeric Up-Down interface tool.  The Action tool replace both "10"s in the filter expression with the user selected value

To learn more about the Interface tools, check out the Creating Analytic Apps Interactive training

Dan

[Akash__on]

Use Sort tool (Descending) and then use sample tool (top 3) Done!! Dynamically always last 3 selected.

Mark as solution, if this helped.

Cheers

Aakash.

[sanastasova]

Akash,

Could you please create a workflow to demonstrate? Because when I select your options I still don't get the right data. 

Thanks!

[Akash__on]

Try this,

Mark as solution, if this helped.

Cheers

Aakash.

[sanastasova]

The problem is that my database contains a lot of rows with repeating weeks and if I do "First 3" it gets me only three rows. See example from excel;

[TeePee]

I don't know if you still need help with this, but I would do it this way:

1) Add a Summarise tool to find "Max" of "Week"

2) Insert an "Append" tool: connect the main workflow to the "Target" input of the Append tool and the Summarise tool output to the "Source" input of the Append tool.  This will add a "Max_Week" column which adds the "Max_Week" value to every row.

3) Now insert your formula tool referencing the max week column eg to limit to the past 3 weeks you could do [Week] <=([Max_Week]-2) (NB, if you are using actual dates rather than simple week numbers, you'd need to use the DateTimeDiff formula instead of the subtraction)