How to automatically get first day of current week?

[soncoku]

Greetings,

I'm new to Alteryx and I'm stuck at this step.

I'm working with the Filter Tool and I want to the filter to select the range 1 week. Currently I'm doing int manually like this:

[Date] >= "2020-06-01" AND
[Date] <= "2020-06-07"

It works just fine but I have to manually select the dates every week.

Now my goal is to make it select the current week automatically.

I was thinking to get the current week automatically from the system and then select the first day of that week as the first value of the range and last day of the current week as the end value. I'm new to alteryx and I'm not being able to achieve the desired result.

If anyone could put me on the right track I'd apreciate it a lot.

Thanks in advance.

[hudyr]

Based on jwalder's solution here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/First-day-of-a-week/m-p/194993/highlig...

It supposes that Sunday is first day of week:

DateTimeAdd(
DateTimeAdd(
DateTimeTrim(
DateTimeNow(),'year')
,ToNumber(DateTimeFormat(DateTimeNow(),'%U'))
*7,'days'),
switch(
DateTimeFormat(
DateTimeAdd
(DateTimeTrim
(DateTimeNow(),'year'),ToNumber(DateTimeFormat(DateTimeNow(),'%U'))*7,'days'),'%a'),0,'Mon',-1,'Tue',-2,'Wed',-3,'Thu',-4,'Fri',-5,'Sat',-6,'Sun',0),'days')

[hudyr]

Close to previous but shorter:

DateTimeAdd(
DateTimeTrim(DateTimeNow(),'year')
,7-ToNumber(DateTimeFormat(DateTimeTrim(DateTimeNow(),'year'),'%u')) +
(ToNumber(DateTimeFormat(DateTimeNow(),'%U'))-1)*7
,'days')

[soncoku]

that solution isn't working for me and judging from that code I highly doubt I'd get something like what I'm trying to acheive.

My goal is to achieve this:

[Date] >= "2020-06-01" AND
[Date] <= "2020-06-07"

BUT AUTOMATICALLY. And monday being the first day of the week.

I attached the a photo to make it clearer, I wan't to acheive that but automatically, for every week.

[hudyr · Accepted solution]

Putting this formula into Custom filter box of Filter tool filters Date variable [DATE] for current week starting on Monday. Also see screenshot.

DateTimeTrim([DATE],'days') >=
DateTimeTrim(
DateTimeAdd(
DateTimeTrim(DateTimeNow(),'year')
,8-ToNumber(DateTimeFormat(DateTimeTrim(DateTimeNow(),'year'),'%u')) +
(ToNumber(DateTimeFormat(DateTimeNow(),'%W'))-1)*7
,'days')
,'days')
AND
DateTimeTrim([DATE],'days') <=
DateTimeTrim(
DateTimeAdd(
DateTimeTrim(DateTimeNow(),'year')
,8-ToNumber(DateTimeFormat(DateTimeTrim(DateTimeNow(),'year'),'%u')) +
(ToNumber(DateTimeFormat(DateTimeNow(),'%W'))-1)*7 +
6
,'days')
,'days')

[soncoku]

You're the man. 

Thanks.

[MNewt9]

Can you use this logic in the initial SQL data pull, not just a filter of already pulled data earlier in the WF or a static file from Excel, a database, etc.?