If statement involving time

[jboschee]

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!

[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?

[MarqueeCrew]

@jboschee,

I am slightly puzzled by the logic and have a version for you to test with.

IF 
	[Actual/Plan] = "ACTUAL" AND  
	( 
	[YEAR] < [Current Year] 
	OR
		([YEAR] = [Current Year] AND
		[MONTH] <= [Current Month]
		)
	)
THEN	"INCLUDE"
ELSEIF
	[Actual/Plan] = "PLAN" AND 
	( 
	[YEAR] > [Current Year]
	OR
	[YEAR] = [Current Year] AND
	[MONTH] >= [Current Month]
	)
THEN	"EXCLUDE"
ELSE	Null()
ENDIF

When the word ACTUAL is present, you must either be a prior year or it can be this year with the current or a prior month.

When the word PLAN is present, you must either be  a future year or it can be this year with the current or a future month.

That's how I conceived the rule.  When you are PLAN and the date is in the past, it is NULL and when you are ACTUAL and the date is in the future you are also NULL.

Cheers,

Mark

[jboschee · Accepted solution]

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