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

Adding days to Current Week

ZoeM
8 - Asteroid

Hi Community.

First time user here. I am developing a workflow and for the life of me I am stomped on how to add days, specifically 7, 14, and 28 days to the current week. 

 

Not sure if I will need to develop the current week field first, then add the days?

 

My current formula is:

 

IF [VPP Dates]<=DateTimeAdd(DateTimeToday(),7,"days") and [VPP Dates]>= DateTimeToday()
and IsNull([Decl Dates]) THEN "One Week" ELSE

IF [VPP Dates]<=DateTimeAdd(DateTimeToday(),14,"days") and [VPP Dates]>= DateTimeToday()
and IsNull([Decl Dates]) THEN "Two Week" ELSE

IF [VPP Dates]<=DateTimeAdd(DateTimeToday(),28,"days") and [VPP Dates]>= DateTimeToday()
and IsNull([Decl Dates]) THEN "Four Week" ELSE

IF [Start_Date]<=DateTimeToday() and IsNull([Decl Dates]) THEN "In-Process" ELSE

"Way Ahead" ENDIF ENDIF ENDIF ENDIF

 

This works like a charm, but its based on today() and I need it to be based on current week ending Friday. I saw a blog on how to create an aggregated week and not sure whether that would be beneficial as a first step?

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Summarize-data-by-the-week/td-p/6002

 

Any help provided will be greatly appreciated!

2 REPLIES 2
danrh
13 - Pulsar

Toss the following into a Formula tool as a new field and then use it instead of DateTimeToday():

 

DateTimeAdd(DateTimeToday(),
Switch(DateTimeFormat(DateTimeToday(),'%a'),0,
	'Sat',6,
	'Sun',5,
	'Mon',4,
	'Tue',3,
	'Wed',2,
	'Thu',1)
,'day')

You could put it all into one formula, but it would kind of long and ugly, so my preference would be to split it into two.

 

ZoeM
8 - Asteroid

Thanks DanrH

That worked like a charm. It gives me next weeks count and I will test on Monday to see if I do get consistency as the week turns.

Much Obliged sir.

Labels