Making dynamic workflow that only shows last week's data

[derm]

Hi, I'm new to alteryx so I'm sorry I'm a little slow with understanding but I'm hoping to receive some help. Currently, I'm trying to create a dynamic workflow that, when ran, would pull last week's data (starting from the Sunday of the week prior up until the current week's Sunday). I know I would need to use filter but I'm not sure how I would do it. Thank you.

[lbardoe · Accepted solution]

Assuming you have a field called Date in your workflow that is formatted as a date you could use the below formula in a filter tool that will extract dates from Sunday to Sunday inclusive:

[Date]>DateTimeAdd(DateTimeToday(),(tonumber(DateTimeFormat(DateTimeToday(),"%u"))+8)*-1,"days") AND
[Date]<DateTimeAdd(DateTimeToday(),(tonumber(DateTimeFormat(DateTimeToday(),"%u")))*-1,"days")

[Raj · Accepted solution]

use formula tool:
 ([Date]>DateTimeAdd(DateTimeToday(),(tonumber(DateTimeFormat(DateTimeToday(),"%u"))+8)*-1,"days")) and  ([Date]<DateTimeAdd(DateTimeToday(),(tonumber(DateTimeFormat(DateTimeToday(),"%u")))*-1,"days")) 

[derm]

Thank you! This makes a lot of sense.

[derm]

Hi, the workflow you helped with worked and I thank you so much for it. I want to make a monthly version of it. How would I do that? Could you explain the steps so I could learn too? I'm stumped with what you'd change "%u" into. Thanks so much.

Best,

derm

[lbardoe]

'%U' brings back the day of the Week (i.e. Monday = 1, Tuesday = 2 etc). For a monthly one  you could use:-

[Date]>=DateTimeAdd(DateTimeFirstOfMonth(),-1,"months") and [Date]<=DateTimeAdd(DateTimeToday(),DateTimeDay(DateTimeToday())*-1,"days")