# Creating calculated field for "Reason for Cancellation"

**URL:** <https://community.amazonquick.com/t/creating-calculated-field-for-reason-for-cancellation/25789>\
**Category:** Q&A\
**Tags:** formulas, analysis, calculations, Business-Intelligence-Engineer, quick-sight\
**Created:** [February 12, 2024, 12:13am UTC](https://community.amazonquick.com/t/creating-calculated-field-for-reason-for-cancellation/25789 "2024-02-12T00:13:43Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Athi\_Path](https://avatars.discourse-cdn.com/v4/letter/a/76d3ee/32.png) [@Athi\_Path](https://community.amazonquick.com/u/Athi_Path)\
**Post date:** [February 12, 2024, 12:13am UTC](https://community.amazonquick.com/t/creating-calculated-field-for-reason-for-cancellation/25789/1 "2024-02-12T00:13:43Z")

</div>

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:

 ![Screenshot 2024-02-11 at 7.05.29 PM](https://d1wl4jav5dp96l.cloudfront.net/original/3X/7/3/7339c70f08e0846c31b0a9132c071f3a5c8eb88c.png)

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!

---

<div class="post-metadata">

**Author:** ![ErikG](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/erikg/32/14524_2.png) [@ErikG](https://community.amazonquick.com/u/ErikG)\
**Post date:** [February 12, 2024, 6:29am UTC](https://community.amazonquick.com/t/creating-calculated-field-for-reason-for-cancellation/25789/2 "2024-02-12T06:29:27Z")

</div>

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

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

```

BR

---

<div class="post-metadata">

**Author:** ![ErikG](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/erikg/32/14524_2.png) [@ErikG](https://community.amazonquick.com/u/ErikG)\
**Post date:** [February 18, 2024, 1:04pm UTC](https://community.amazonquick.com/t/creating-calculated-field-for-reason-for-cancellation/25789/3 "2024-02-18T13:04:59Z")

</div>

Hi @Athi_Path  
any update on your side?  
BR

---

<div class="post-metadata">

**Author:** ![Wakana](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wakana/32/3520_2.png) [@Wakana](https://community.amazonquick.com/u/Wakana)\
**Post date:** [February 22, 2024, 2:40am UTC](https://community.amazonquick.com/t/creating-calculated-field-for-reason-for-cancellation/25789/4 "2024-02-22T02:40:53Z")

</div>

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