If statement involving time

Hi everyone,

I'm trying to put together an if statement from my excel file and put it in Alteryx. The excel formula looks like this:

=IF(AND(Actual/Plan="Actual",OR(Year<Current Year,AND(Year=Current Year,Month<=Current Month))),"Include",IF(AND(Actual/Plan="Plan",OR(Year>Current Year,AND(Year>Current Year,Month>Current Month))),"Include","Exclude"))

This is what I currently have and receive a Malformed Function Call error

IF CONTAINS([Actual/Plan]="ACTUAL",[YEAR]<[Current Year],[MONTH]<=[Current Month])THEN "INCLUDE"

AND

IF CONTAINS([Actual/Plan]="Plan",([YEAR]>[Current Year]),([YEAR]>=[Current Year]),([MONTH]>[Current Month]),"Include","Exclude"

ELSEIF Null()
ENDIF

All fields are currently V_String

Any help is appreciated!

Tips and Tricks

Accepted answers

jboschee

Here's the formula that I used that worked.

IF CONTAINS([Actual/Plan],"ACTUAL")THEN "INCLUDE"

ELSEIF [YEAR]<[Current Year] AND [MONTH]<=[Current Month]THEN "INCLUDE"

ELSEIF CONTAINS([Actual/Plan],"Plan") AND [YEAR]>=[Current Year] AND [MONTH]>[Current Month] THEN "INCLUDE"

ELSE "Exclude"
ENDIF

All comments

ddiesel

Hi @jboschee!

First, use the Select tool to convert the V_String fields for "YEAR", "Current Year", "Current Month", and "MONTH" to numeric fields. I imagine that your data best fits Int32, but I'm not sure without seeing a sample. We cannot perform calculations on string fields, so the fields must be converted to numeric for your formula to work.

I adjusted your formula slightly. Let me know if this one works:

IF CONTAINS([Actual/Plan],"ACTUAL") AND [YEAR]<[Current Year] AND [MONTH]<=[Current Month] THEN "INCLUDE"

ELSEIF CONTAINS([Actual/Plan],"Plan") AND [YEAR]>[Current Year] AND [YEAR]>=[Current Year] AND [MONTH]>[Current Month] THEN "Exclude"

ELSE Null()
ENDIF

jboschee

Hi @ddiesel

Thanks for the info. I ran the formula and only the "include" came through. Otherwise it was blank (null). Any idea why the "exclude" isn't coming through?

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167