Multirow formula

[Lizbhernan]

Hello, can someone tell me what's wrong with my formula?

IF [Employee ID] != [Row-1:Employee ID] THEN "First Leave"
ELSEIF !ISNULL([Absence Date]) AND !ISNULL([Last Day of Leave - Actual]) THENIF [Last Day of Leave - Actual] <= [Absence Date] THEN "Concurrent Leave" ELSEIF ([Last Day of Leave - Actual] - [Absence Date]) >= 0 THENIF ([Last Day of Leave - Actual] - [Absence Date]) <= 14 THEN "New Leave (0-14 days gap)" ELSE "New Leave (15+ days gap or next)" ENDIF ELSE "Not New Leave" endif else "Insufficient Data" ENDIF

Once I add the ENDIF at the end, it starts to grey out towards the bottom :( 

Can someone help?

[binuacs]

@Lizbhernan Try the below formula

IF [Employee ID] != [Row-1:Employee ID] THEN
"First Leave"
ELSEIF !ISNULL([Absence Date]) AND !ISNULL([Last Day of Leave - Actual]) THEN
IF [Last Day of Leave - Actual] <= [Absence Date] THEN
"Concurrent Leave"
ELSEIF ([Last Day of Leave - Actual] - [Absence Date]) >= 0 THEN
IF ([Last Day of Leave - Actual] - [Absence Date]) <= 14 THEN
"New Leave (0-14 days gap)"
ELSE
"New Leave (15+ days gap or next)"
ENDIF
ELSE
"Not New Leave"
ENDIF
ELSE
"Insufficient Data"
ENDIF

[Lizbhernan]

@binuacs Hi, it didn't work. I did find a solution, however, it is not working as intended 😞 I am including a sample of the workflow I am working on and my desired results. I am hoping this might give you an idea of what I am trying to do and get some assistance.

[binuacs]

@Lizbhernan, can you provide the logic for the  Leave Type Classification 2 given below?

Leave Type Classification 2

First Leave

Consecutive Leave

Insufficient Data

Concurrent Leave

New Leave (15+ days gap or next)

[Lizbhernan]

@binuacs Yes, this is my excel formula 

=IF(E2<>E1,"First Leave",
IF(AND(ISNUMBER(J2),ISNUMBER(K1)),
IF(J2<=K1,"Concurrent Leave",
IF(J2-K1>=0, IF(J2-K1<=14,"New Leave (0-14 days gap)","New Leave (15+ days gap or next)"),
"Not New Leave")),
"Insufficient Data"))

[binuacs · Accepted solution]

@Lizbhernan I am getting a different result after applying the above formula in alteryx 

Note: the excel formula says take teh difference of AbsenceDate and Last Day of LEave - Actual, but i given the opposite way, 

IF [Employee ID] != [Row-1:Employee ID] THEN
    "First Leave"
ELSEIF !ISNULL([Absence Date]) AND !ISNULL([Last Day of Leave - Actual]) THEN
    IF [Last Day of Leave - Actual] <= [Absence Date] THEN
        "Consecutive Leave"
    ELSEIF DATETIMEDIFF([Last Day of Leave - Actual] , [Absence Date],'days') >= 0 THEN
        IF DATETIMEDIFF([Last Day of Leave - Actual] , [Absence Date],'days') <= 14 THEN
            "New Leave (0-14 days gap)"
        ELSE
            "New Leave (15+ days gap or next)"
        ENDIF
    ELSE
        "Not New Leave"
    ENDIF
ELSE
    "Insufficient Data"
ENDIF

[apathetichell]

THENIF is not a thing. Anywhere. So logically we have:

IF.

Then.

elseif

Then

elseif...

else.

endif

logically what you are referring to as thenif - is actually a nested elseif---> and you can only have one endif.... My general recommendation here is smaller statements. test smaller parts that work --> and then build off of that. Alteryx usually doesn't penalize you for using more formula tools.

[Lizbhernan]

This worked @binuacs TYSM 😊