Hello,
I'm trying to write a nested IF statement, in which the datediff is either calculated between a creation date and the completion date, or in the event that the completion date does not exist, then the creation date and the current date.
My code is as follows (however I am receiving a 'malformed if statement' error using it):
IF ISNULL([Completion by Date]) THEN
IF DateTimeDiff(DateTimeToday(), [Created on] ,'days') > 59
THEN 'Aging'
ELSEIF DateTimeDiff(DateTimeToday(), [Created on],'days') > 45
THEN 'Near Aging'
ELSE Null()
ENDIF
ELSEIF !ISNULL([Completion by Date]) THEN
IF DateTimeDiff([Completion by Date],[Created on],'days') > 59
THEN 'Aging'
ELSEIF DateTimeDiff([Completion by Date], [Created on],'days') > 45
THEN 'Near Aging'
ELSE Null()
ENDIF
Any help is greatly appreciated.
Thanks,
Alefiyah
Hey @Alefiyah,
There is a few things going on here. First statments like
IF ISNULL([Completion by Date]) THEN
IF DateTimeDiff(DateTimeToday(), [Created on] ,'days') > 59
THEN 'Aging'
Can be combined into (for simplification):
IF ISNULL([Completion by Date]) AND DateTimeDiff(DateTimeToday(), [Created on] ,'days') > 59
THEN 'Aging'
And second you have two IF statements separate from each over so eg. you ENDIF in the middle. I don't think Alteryx can interpret this as you are just filling One value.
Try this:
IF ISNULL([Completion by Date]) AND DateTimeDiff(DateTimeToday(), [Created on] ,'days') > 59
THEN 'Aging'
ELSEIF DateTimeDiff(DateTimeToday(), [Created on],'days') > 45
THEN 'Near Aging'
ELSEIF !ISNULL([Completion by Date]) AND
DateTimeDiff([Completion by Date],[Created on],'days') > 59
THEN 'Aging'
ELSEIF DateTimeDiff([Completion by Date], [Created on],'days') > 45
THEN 'Near Aging'
ELSE Null()
ENDIF
The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...
Any questions or issues please ask
Ira Watt
Technical Consultant
Watt@Bulien.com
@Alefiyah
May I comment that for conditional statement, I believe the point is that it should alwasy cover all the conditions.
in you case, > 59 is condition1, then > 45 is condition2 so the correct way should be
1. condition1: A >59
2. Condition 2: A<= 59 and A>45
3. Condition 3: else
otherwise, Say a number 60 will alwas be trapped by condition 2.
if the expression sort nicely. it will not a problem. but it still good to have for safety purpose.
and we can shorten the formula further, since the condition and text is same.
and better to add condition for less the 45 and blanks.
@Pang_Hee_Choy
Nice one.
I think the Switch function is better fit for this one. Thanks! 😉