Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Using if then statement to convert date to quarter

Highlighted
5 - Atom

I'm trying to convert a column of data called fiscal month to change to say which quarter it falls in.  Below is what I'm using, and it doesn't convert the dates the respective quarter.  The fiscal month is a V_String type.  Also I'm very new to Alteryx, so if you answer, please phrase it in an Alteryx for dummies way

 

IF [Fiscal Month] = "2019-07-01"
THEN "Q1"
ELSEIF [Fiscal Month]= '2019-08-01'
THEN "Q1"
ELSEIF [Fiscal Month]="2019-09-01"
THEN "Q1"
ELSEIF [Fiscal Month]="2019-10-01"
THEN "Q2"
ELSEIF [Fiscal Month]="2019-11-01"
THEN "Q2"
ELSEIF [Fiscal Month]="2019-12-01"
THEN "Q2"
ELSEIF [Fiscal Month]="2020-01-01"
THEN "Q3"
ELSEIF [Fiscal Month]="2020-02-01"
THEN "Q3"
ELSEIF [Fiscal Month]="2020-03-01"
THEN "Q3"
ELSEIF [Fiscal Month]="2020-04-01"
THEN "Q4"
ELSEIF [Fiscal Month]="2020-05-01"
THEN "Q4"
ELSEIF [Fiscal Month]="2020-06-01"
THEN "Q4"
ELSE [Fiscal Month]
ENDIF

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @MG007 are you updating an existing column in a formula tool? Your IF formula works if you create a new column in the formula tool. I've attached a workflow.

Highlighted
Alteryx Certified Partner
 
 
 

You can also do it like this,

 

wfwf

 

 

But as @JosephSerpis you probably just needed to create a new field as you can't overwrite with a different data type in the Formula Tool.

 

Highlighted
17 - Castor
17 - Castor

Hi @MG007 

 

If you use

 

"Q" + tostring(ceil(DateTimeMonth(DateTimeAdd([Fiscal Month],-6,"months"))/3))

 

in a formula you'll get the proper quarter with the added benefit that it will work for any year without having to change the formula

 

 

DateTimeAdd([Fiscal Month],-6,"months") subtracts 6 months from the fiscal month to account for the fact that your year starts in July 

DateTimeMonth(XXX)/3 extracts the month number and divides this by 3 since there are 3 months in the quarter

ceil(XXX).  gives you the next largest integer

tostring(XXX) converts the number to a string

 

The results of this are 

 

Q.png

 

Dan

 

 

Highlighted
17 - Castor
17 - Castor

Thanks for the thumbs up @PhilipMannering.  Did the community change from Stars to Kudos while I was working?

 

Dan

Highlighted
Alteryx Certified Partner

They've gone full facebook. Definitely seems like a recent thing.

Labels