Conditional Statements

[Ahasan13]

Hello,

I need to write a statement that will do the following:

Example: 

Date                Quarters 

3/31/2020 -->  Q4 (of the previous years last quarter) 

4/31/2020 -->  Q1 (of the previous quarter)

5/31/2020 -->  Q1 (still the previous quarter is Q1)

7/31/2020 ..> Q2 (the previous quarter is Q2)

What I need is based on the last day of the quarter, I need it to spit out what the prior quarter is. I also need this to work for 2020, 2021, and 2022. Not sure exactly how to write this or what tool I should use. Still very new in to Alteryx.

[damalraj22]

Hi, 

You can use the formula tool and create an IF THEN function to specify which months would belong to the respective prior quarter. I attached a sample workflow with the formula you can use. Reach back out if you have any other questions. 

[Ahasan13]

Not sure how to read that but this is how I did it and its not working:

EOM = End of Month 

IF [EOM] <= "2020-31-03" THEN "Q4" Else "" Endif

IF [EOM] <= "2020-30-06" THEN "Q1" Else "" Endif

IF [EOM] <= "2020-30-09" THEN "Q2" Else "" Endif

IF [EOM] <= "2020-31-12" THEN "Q3" Else "" Endif

IF [EOM] <= "2021-31-03" THEN "Q4" Else "" Endif

IF [EOM] <= "2021-30-06" THEN "Q1" Else "" Endif

IF [EOM] <= "2021-30-09" THEN "Q2" Else "" Endif

IF [EOM] <= "2021-31-12" THEN "Q3" Else "" Endif

IF [EOM] <= "2022-31-03" THEN "Q4" Else "" Endif

IF [EOM] <= "2022-30-06" THEN "Q1" Else "" Endif

This is under the formula tool, and I used to "+" button to keep adding formulas so that It'd update on the same field. But this is essentially how I want to spit out above. Please help! 

[damalraj22]

Hi, 

They syntax you are using is incorrect. Change it to the format below: 

if [EOM] <= "2020-31-03" THEN "Q4"
elseif [EOM] <= "2020-30-06" THEN "Q1"
else "Q3"
ENDIF

[PanPP]

Hi @Ahasan13 

Another approach you can use is shown in the image below.

Hope this helps, if it does please like this post and if it helps resolve your problem, mark it as a solution. If you have any other questions, please let us know.

[Ahasan13]

if [EOM] <= "2020-31-03" THEN "Q4"
elseif [EOM] <= "2020-30-06" THEN "Q1"
else "Q3"
ENDIF

Above woks but I need to add another condition where it will capture "2020-30-09" which should be "Q2", and the rest of year "2020" should be "Q3" I tried to use "elseif" right after where you had "Q1" and it did said it was an incorrect format. After I figure this out, can I add the same formula by hitting the "+" and just change date to "2021&2022" and it would essentially spit out the same information but for "2021&2022"? Please note that the 2020, 2021, 2022 data are stacked on top of each other. Vey new to Alteryx so trying to figure this out. Thanks!