Label date as current week or previous week

[jark1294]

I'm trying to set up a formula that will return a value that states whether the date of a line item is within the current week, previous week or none of the above. For example:

Week of 3/18/2019

Pulling data on 3/25/2019 (today's date at the time)

A line item with Day = 3/20/2019 would return "Current Week"

A line item with Day = 3/13/2019 would return "Previous Week"

A line item with 1/20/2019 would return "N/A"

I started with this formula but it isn't quite working. Anyone know??

if DateTimeDiff([Day],DateTimeToday(),'days') <= 7 THEN "Current Week" ELSEIF "No"

ENDIF

[jark1294]

Additional context - I'm creating this field to eventually be used in Power BI for a week over week formula

[CharlieS]

Hi @jark1294. I have a few suggestions for ya here.

1) Your input dates are formatted "MM/DD/YYYY", but Alteryx date formulas will expect ISO8601 format, "YYYY-MM-DD". The first step is to convert the date with the following formula. The idea is that you're telling Alteryx to parse this into the standard format and you provide a guide of how it's formatted in the expression.

DateTimeParse([Day],"%m/%d/%Y")

2) The DateTimeDiff( expression will always perform a subtraction. In your formula, the result of <2019-03-20> - <2019-03-25> is -5. I don't think this was the value you were expecting. There are two options, use the absolute value with the abs( expression, or swap the dates in the formula. I prefer the absolute value method.

3) The IF statement syntax is IF <condition1> THEN <result1> ELSEIF <condition2> THEN <result2> ELSE <result3> ENDIF. In your formula, you should replace "ELSEIF" with just "ELSE". Beyond that, I added a second condition for the "Previous Week" result. 

Try this formula:

IF abs(DateTimeDiff(DateTimeParse([Day],"%m/%d/%Y"),DateTimeToday(),'days')) <= 7 THEN "Current Week"

ELSEIF (abs(DateTimeDiff(DateTimeParse([Day],"%m/%d/%Y"),DateTimeToday(),'days')) >=8
AND
abs(DateTimeDiff(DateTimeParse([Day],"%m/%d/%Y"),DateTimeToday(),'days')) <= 14)
THEN "Previous Week"

ELSE "N/A" ENDIF

Check out the attached example and let me know if this works for you. 

[jark1294 · Accepted solution]

Thanks @CharlieS ! I thought when i was typing the question that the way i was typing the dates might trip people up, should have clarified that was just an example.

I ended up with the following. Because we're starting with the current date i changed it to -7 and greater than, and same thing for next ones. I think it worked!

if DateTimeDiff([Day],DateTimeToday(),'days') >= -7 THEN "Current Week" ELSEIF

( DateTimeDiff([Day],DateTimeToday(),'days') <= -8

AND

DateTimeDiff([Day],DateTimeToday(),'days') >= -14)

THEN "Previous Week"

ELSE

"No"

ENDIF

[ChrisTX]

Are you sure you want to use DateTimeDiff, and not include logic to consider if dates cross over a weekend?

If you compare a Monday to a Friday with DateTimeDiff, you get 3 days.  Would you want Monday to be "current week" and Friday to be "previous week"?

DateTimeDiff("2019-03-25","2019-03-22","day") = 3

2019-03-25 = Monday

2019-03-22 = Friday