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?
Solved! Go to Solution.
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).
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.
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
@patrick_mcauliffe In which part? Everywhere in that code?
Could you do it in the code and copy paste it for me?
@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.
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')