Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Weekly range

soncoku
9 - Comet

Hello eveyone.

I have this code in the Filter Tool. 

It takes the current week and turns it into a range, from Monday to Sunday (in dates).

It works fine but I need to make a change or probably add another filter.

 

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

 

 

 

 

Now here's the problem.

Data gets added during the each day when an actions is performed. And I can't do the report on Sunday. So I need to do it Monday morning.

But when I do it on Monday the script above updates the dates range and put this week as range. This week is empty ofcourse.

 

So I want to be able to Run the workflow on Monday and get the date range Monday (date) and Sunday (date) of the past week.

if it's Tuesday or any other day it could take this week as range it's fine, just on Mondays I need the past week as range.

 

Does any one have any ideas?

5 REPLIES 5
patrick_mcauliffe
14 - Magnetar
14 - Magnetar

I've done something like this before.

 

On the left hand side, in the workflow configuration, set a variable named DaysAgo (or anything of your choice).

Click the Plus sign, then type it in the box that appears.  In the right column, type zero (0).

patrick_mcauliffe_0-1592304411283.png

 

 

Now, in your formula, replace:

DateTimeNow()

 

with:

DateTimeAdd(DateTimeNow(),[User.DaysAgo],'day')

 

On Monday morning, for your first run, change the variable in the workflow config from zero (0) to negative 1 (-1) or minus the number of days that you need it to be retroactive.

 

RolandSchubert
16 - Nebula
16 - Nebula

Hi @soncoku ,

 

you could replace DateTimeNow() by DateTimeAdd(DateTimeNow(), DateTimeFormat(DateTimeNow(), '%u') = '1', 'day'). On Mondays, DateTimeFormat(DateTimeNow(), '%u') = '1' will be true and return "-1", i.e. Sunday.

 

Let me know if it works for you.

 

Best,

 

Roland

soncoku
9 - Comet

@patrick_mcauliffe  In which part? Everywhere in that code?

Could you do it in the code and copy paste it for me?

soncoku
9 - Comet

@RolandSchubert Could you please replace in the code i posted. Because it either doesn't work or it did something wrong.
p.s I changed the date on the computer to Monday, yesterday.

RolandSchubert
16 - Nebula
16 - Nebula

I've changed DateTimeNow() to the new statement - give it a try.

 

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

Labels