alteryx Community

Alteryx Designer Desktop Discussions

SOLVED

Filter data by last week

8 - Asteroid

How can i create a filter that grabs records from last week? Like starting sunday and ending sunday, not past 7 days.

11 REPLIES 11
17 - Castor

Hi @Dan5,

Hope you are well.

I would suggest using the filter in order to filter for the previous week number.

I found a solution that created a line for each day and a week number analysing this might be useful in approaching this problem:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculating-the-week-number/m-p/12245/...

Good luck!

8 - Asteroid

I am currently using this, but it is for current week, need to modify it so i can grab the last (and full) week, regardless of when i run it during current week.

ToNumber(DateTimeFormat([CREATEDDATE],'%U'))=ToNumber(DateTimeFormat(DateTimeToday(),'%U'))
AND DateTimeYear([CREATEDDATE])=DateTimeYear(DateTimeToday())

Alteryx

You can find the date of the last Friday for example by using something like this in a formula tool:

Switch(DateTimeFormat(DateTimeNow(),'%a'),0,
'Sat',-1,
'Sun',-2,
'Mon',-3,
'Tue',-4,
'Wed',-5,
'Thu',-6,
'Fri',-7),
"days")

You can use two of these formulas where you adjust so that it always pulls the most recent Sunday and the Sunday before. Then you could leverage these in a filter tool where you compare your date column to these dates.

8 - Asteroid

hmm, simply subtracted 1, and it works:

ToNumber(DateTimeFormat([CREATEDDATE],'%U'))=ToNumber(DateTimeFormat(DateTimeToday(),'%U'))-1
AND DateTimeYear([CREATEDDATE])=DateTimeYear(DateTimeToday())

I am concerned that will will not work as we pass through to 2021.

Can anyone confirm, comment?

8 - Asteroid

thanks, that seems like a good idea, I am not sure how to format the statement though; say i want to grab rows created between last sunday and the sunday before that one.

Switch(DateTimeFormat(DateTimeNow(),'%a'),0,'Sun',-3,"days")

Something close to that?

Alteryx

Something like the screenshot below. Workflow is attached for reference.

Alteryx

Technically you can build this all into the filter I find that sometimes breaking it out into separate pieces helps for troubleshooting.

8 - Asteroid

thanks, interesting that combined into a filter i get 7 records not 8:

Switch(DateTimeFormat(DateTimeNow(),'%a'),0,
'Sat',-13,
'Sun',-14,
'Mon',-8,
'Tue',-9,
'Wed',-10,
'Thu',-11,
'Fri',-12),
Switch(DateTimeFormat(DateTimeNow(),'%a'),0,
'Sat',-6,
'Sun',-7,
'Mon',-1,
'Tue',-2,
'Wed',-3,
'Thu',-4,
'Fri',-5),
"days")

Also, i am curious to know if this solution would work on a rolling basis. It seems i would have to update the number every week. Am I misunderstanding?

Alteryx

This uses DateTimeNow() which pulls the current date time, so this is a dynamic formula. The reason you are likely getting 7 records rather than 8 is because of the time component. In my example that I showed, I set the field to be a date. You could overcome this by wrapping the DateTimeNow() with a ToDate(DateTimeNow())

Labels