How to automatically get first day of current week?

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.

Date Time

Accepted answers

hudyr

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

in_current_week.PNG

All comments

hudyr

Based on jwalder's solution here:

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

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.

Capture.PNG

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.?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703