Filter data by last week

[Dan5]

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

[Emil_Kos]

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!

[Dan5]

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())

[BrandonB]

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

DateTimeAdd(DateTimeNow(),
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. 

[Dan5]

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?

[Dan5]

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. 

[CREATEDDATE]< DateTimeAdd(DateTimeNow(),
Switch(DateTimeFormat(DateTimeNow(),'%a'),0,'Sun',-2,"days") and [CREATEDDATE]> DateTimeAdd(DateTimeNow(),
Switch(DateTimeFormat(DateTimeNow(),'%a'),0,'Sun',-3,"days")

Something close to that?

[BrandonB · Accepted solution]

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

 

[BrandonB]

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

[Dan5]

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

[Field1] >= DateTimeAdd(DateTimeNow(),
Switch(DateTimeFormat(DateTimeNow(),'%a'),0,
'Sat',-13,
'Sun',-14,
'Mon',-8,
'Tue',-9,
'Wed',-10,
'Thu',-11,
'Fri',-12),
"days") AND [Field1] <= DateTimeAdd(DateTimeNow(),
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?

[BrandonB · Accepted solution]

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())