Week Start formula?

[robl93]

Hi all,

So I'm trying to filter my data by month end and week start. I'm trying to export two databases, one for each filter. 

I read about how to filter dates by month end, but I couldn't find anything in regards to week start. I know in excel it would be = date - weekday(date, 2)+1, but what about Alteryx?

For week start, what I'm asking is...if I have 1/25/2017 (which is a wednesday), after the week start formula is input, it would show 1/23/2017 (which is the start of that week, a monday).

Thanks!

[jgo]

Hi @robl93,

It's pretty much the same logic you use in Excel, just utilizing Alteryx's syntax.

'2017-01-23' = DATETIMEADD('2017-01-25',-TONUMBER(DATETIMEFORMAT([Date],'%w'))+1,'days')

Depending on which week Sundays are to be accounted in, you may need to make adjustments to address. the "%w" specifier is what returns an day number of week, which is a value between 0-6, starting on Sunday.

Hope this helps!

[robl93]

If I'm understanding correctly, I'd like Monday's to be the start of the week, and Sunday's to be day 7.

Also, I'd like this formula to be applied to all the dates in my database (800,000+)

[jgo · Accepted solution]

DATETIMEADD([Date],-
IF
TONUMBER(DATETIMEFORMAT([Date],'%w'))=0 
THEN 7 
ELSE TONUMBER(DATETIMEFORMAT([Date],'%w'))
ENDIF
+1,'days')

Here's a more complex formula that validates the day number. If it's 0 then change to 7 else keep value.

The first example I provided hard coded the date in the first argument of DATETIMEADD. simply replace that with the field it needs to reference. On the code I attached here, you'd need to replace all the "[Date]" with your field name (illustrated below).

[robl93]

I'll give this a go and report back, thanks!

[robl93]

Hey jgo,

Your formula was perfect!

I actually tried to do month end just now and realized I had no idea how to do it either. I'm a total SQL noob. Basically what I'm trying to do for month end is...

If I have 2016-11-29 as a date (and thousands of other dates with varying days and months and years), I would like the formula to give me the month end of that particular date. So in this case, it would show 2016-11-30.

Thanks in advance once again.

[RodL]

That one should be a bit easier...there is a DateTimeLastOfMonth() function available in Alteryx.

[robl93]

Ah yes, I figured it out. I used:

DateTimeTrim([visitdate],"firstofmonth")

[dniedrauer]

Simplest formula: 

datetimeadd([Date],tonumber("%u")*-1,'days')