Filter only for last 3 weeks

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.

Data Investigation

Accepted answers

All comments

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

FilterWeeks.yxmd

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

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.

last 3 weeks always.yxmd

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;

week.xlsx

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)

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335