Creating calculated field for "Reason for Cancellation"

[Athi_Path]

Hello Folks

Hope someone can help me here, I have a Table with “Caller” (String), “Created” (Date), and “Status” (String) columns. I need to create a Calculated Field column for “Reason for Cancellation” where it matching certain conditions.

Here is my Table:

And here are the conditions for “Reason for Cancellation”

Conditions:

• “If the ‘caller’ field has more than one identical value, and the ‘caller’ values were created within 120 seconds of each other, and the status is ‘Cancelled,’ then the reason for cancellation is ‘Double Entry’.”

• “If the ‘caller’ field has a single value or they have the same values but were created more than 120 seconds apart, and the status is ‘Cancelled,’ then the reason for cancellation is ‘Customer Left’.”

• “If the ‘caller’ field is Mary and the status is ‘Cancelled,’ then the reason for cancellation is ‘Testing’.”

Can someone help me to create the correct formula for calculated field please? I have being trying figure this out my own for last two days. Thank You!

[ErikG]

Hi @Athi_Path
maybe you can share the sample data or even build your analysis in Arena and share it?

I would say you would need different parts.

1. “Caller count” → count({caller})
2. “Date” → lastValue({Created}, [{Created} DESC]),[{caller}]

For you first condition it could look something like

ifelse(count({caller})>1 AND dateDiff({Created},lastValue({Created}, [{Created} DESC]),[{caller}],"SS" AND {Status} = "Cancelled", "Double Entry", next contition)

BR

[ErikG]

Hi @Athi_Path
any update on your side?
BR

[Wakana]

hi @Athi_Path

Did the solution ErikG suggested work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved

kind regards,
Wakana