Formula to get expected output

PNC97

I'm using a formula to calculate OT by the below logic

if [OT] > 0 and [duplicat] = "ok" then

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] <= [tNight.Start] then 0 else

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] > [tNight.Start] then

(if [Punch-Out] >=24 then 24 else [Punch-Out]) - [tNight.Start] endif

else

(if [Punch in- OT] >= [tNight.Start] and [Punch in- OT] < 24 and [Punch-Out] >= 24 then 24 - [Punch in- OT] else

(if [Punch in- OT] >= [tNight.Start] and [Punch-Out] <24 then [Punch-Out] - [Punch in- OT] else 0
endif)
endif)
endif)
endif)

else 0 endif

I'm getting the output as 24 but expected output is 2

I have attached the data of input and expected output.

Thanks in advance 🙂

Book1.xlsx

Workflow

Developer

Developer Tools

Accepted answers

PNC97

Got solution with the below formula.

if [OT] > 0 and [duplicat] = "ok" then

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] <= [tNight.Start] then 0 else

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] > [tNight.Start] then

(if [Punch-Out] >=24 then 24 else [Punch-Out] endif) - [tNight.Start]

else

(if [Punch in- OT] >= [tNight.Start] and [Punch in- OT] < 24 and [Punch-Out] >= 24 then 24 - [Punch in- OT] else

(if [Punch in- OT] >= [tNight.Start] and [Punch-Out] <24 then [Punch-Out] - [Punch in- OT] else 0
endif)
endif)
endif)
endif)

else 0 endif

Thanks all 🙂

All comments

T_Willins

Hi @PNC97,

First, I'm not sure how you were getting results as your formula syntax was incorrect. I corrected it to below (bold added for emphasis for discussion below):

If [OT]<=0 THEN 0 ELSEIF
[duplicat] != "ok" THEN 0 ELSEIF
[Punch in- OT] <= [tNight.Start] AND [Punch-Out] <= [tNight.Start] THEN 0 ELSEIF
[Punch in- OT] <= [tNight.Start] AND [Punch-Out] > [tNight.Start] AND [Punch-Out1] >=24 THEN 24 ELSEIF
[Punch in- OT] <= [tNight.Start] AND [Punch-Out] > [tNight.Start] AND [Punch-Out1] <24 THEN [Punch-Out1] - [tNight.Start] ELSEIF
[Punch in- OT] >= [tNight.Start] AND [Punch in- OT] < 24 and [Punch-Out1] >= 24 then 24 - [Punch in- OT] ELSEIF
[Punch in- OT] >= [tNight.Start] and [Punch-Out] <24 then [Punch-Out1] - [Punch in- OT] ELSE 0
ENDIF

All the records in the sample provided fall out under the bolded section above, which provides a result of 24. Hopefully this makes it easier for you to correct the formula, but if not, please provide further clarification on how the formula should be calculating. IF/THEN/ELSEIF formulas in Alteryx will always follow the format of IF... THEN... ELSEIF... THEN... (Repeat ELSEIF/THEN as needed) ELSE... ENDIF

Maskell_Rascal

Hi @PNC97

To piggyback off of what @T_Willins's response, here is your formula written out in Excel. It is operating exactly as expected with the results coming back as "24". Your operation of logic is off.

PNC97

@T_Willins

I had used the formula like this

if [OT] > 0 and [duplicat] = "ok" then

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] <= [tNight.Start] then 0 else

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] > [tNight.Start] then

if [Punch-Out] >=24 then 24 else [Punch-Out] - [tNight.Start] endif

else

(if [Punch in- OT] >= [tNight.Start] and [Punch in- OT] < 24 and [Punch-Out] >= 24 then 24 - [Punch in- OT] else

(if [Punch in- OT] >= [tNight.Start] and [Punch-Out] <24 then [Punch-Out] - [Punch in- OT] else 0
endif)
endif)
endif)
endif)

else 0 endif

but using this formula I'm getting 24 as output but the expected output of PQ is 2 as output.

PNC97

I applied the formula in power query

if [OT] > 0 and [duplicat] = "ok" then

(if [#"Punch in- OT"] <= [tNight.Start] and [#"Punch-Out"] <= [tNight.Start] then 0 else

(if [#"Punch in- OT"] <= [tNight.Start] and [#"Punch-Out"] > [tNight.Start] then (if [#"Punch-Out"] >=24 then 24 else[#"Punch-Out"])-[tNight.Start] else

(if [#"Punch in- OT"] >= [tNight.Start] and [#"Punch in- OT"] < 24 and [#"Punch-Out"] >= 24 then 24-[#"Punch in- OT"] else

(if [#"Punch in- OT"] >= [tNight.Start] and [#"Punch-Out"] <24 then [#"Punch-Out"]-[#"Punch in- OT"] else 0))))

else 0

and the output i got is below

Maskell_Rascal

Hi @PNC97

If you'd like us to help with the formula, please explain the IF/THEN order of logic we need to follow. It is unclear from the formulas you provided, since they were not written the way a typical Alteryx formula would be structured.

PNC97

Got solution with the below formula.

if [OT] > 0 and [duplicat] = "ok" then

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] <= [tNight.Start] then 0 else

(if [Punch in- OT] <= [tNight.Start] and [Punch-Out] > [tNight.Start] then

(if [Punch-Out] >=24 then 24 else [Punch-Out] endif) - [tNight.Start]

else

(if [Punch in- OT] >= [tNight.Start] and [Punch in- OT] < 24 and [Punch-Out] >= 24 then 24 - [Punch in- OT] else

(if [Punch in- OT] >= [tNight.Start] and [Punch-Out] <24 then [Punch-Out] - [Punch in- OT] else 0
endif)
endif)
endif)
endif)

else 0 endif

Thanks all 🙂

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335