Hi there,
I have a database that looks something like this:
Date | Stage | Stage 1 Date | Stage 2 Date | Stage 3 Date |
2019-04-04 | 01/06/2022 | 01/10/2022 | 1/1/2018 | |
2022-06-01 | 01/06/2022 | 01/10/2022 | 1/1/2018 | |
2022-10-08 | 01/06/2022 | 01/10/2022 | 1/1/2018 | |
2020-03-18 | 01/06/2022 | 01/10/2022 | 1/1/2018 | |
2018-09-07 | 01/06/2022 | 01/10/2022 | 1/1/2018 | |
2010-11-09 | 01/06/2022 | 01/10/2022 | 1/1/2018 |
I want to fill in the "Stage" column based on the month and year in the "Date" column - for example, for dates occurring in June 2022, I want "Stage 1" and in October 2022, I want "Stage 2":
Date | Stage |
2019-04-04 | |
2022-06-01 | Stage 1 |
2022-10-08 | Stage 2 |
2020-03-18 | |
2018-09-07 | |
2010-11-09 |
I've tried an IF formula in Alteryx (outputting to the "Stage" column):
IF DateTimeYear([Date]) = DateTimeYear([Stage 1 Date]) && DateTimeMonth([Date]) = DateTimeMonth([Stage 1 Date]) THEN "Stage 1" ELSE "" ENDIF
The above formula works when I'm assigning stage by year only (i.e., using the formula IF DateTimeYear([Date]) = DateTimeYear([Stage 3 Date]) THEN "Stage 3" ELSE "" ENDIF) but seems to break when I bring in the "Month" requirement (I just get a column full of blanks) - any ideas?
Thanks in advance!
Solved! Go to Solution.
Try using this formula
IF DateTimeYear([Date]) = DateTimeYear([Stage 1 Date]) && DateTimeMonth([Date]) = DateTimeMonth([Stage 1 Date]) THEN "Stage 1"
ELSEIF DateTimeYear([Date]) = DateTimeYear([Stage 2 Date]) && DateTimeMonth([Date]) = DateTimeMonth([Stage 2 Date]) THEN "Stage 2"
ELSE ""
ENDIF
Note:
sample data, the Stage 3 date is in the year 2018, while all the other stage dates are in 2022. If this is not a mistake, you will need to add an additional ELSEIF statement for Stage 3 with the correct year and month criteria.
Hi @eleee3
One way of doing this...
IF DateTimeYear([Date]) = DateTimeYear(datetimeparse([Stage 1 Date],"%d/%m/%y")) AND DateTimeMonth([Date]) = DateTimemonth(datetimeparse([Stage 1 Date],"%d/%m/%y"))
THEN "Stage 1"
ELSEIF DateTimeYear([Date]) = DateTimeYear(datetimeparse([Stage 2 Date],"%d/%m/%y")) AND DateTimeMonth([Date]) = DateTimemonth(datetimeparse([Stage 2 Date],"%d/%m/%y"))
THEN "Stage 2"
ELSE null()
ENDIF
Many thanks
Shanker V