Calculated field- ifelse

I have a table having Status Codes as (CG,RT,‘AB’,‘CW’,‘CP’,‘CS’ and Null).

I tried with the below Calculated field for my Donut chart. The values are not matching for {STATUS CD} <> ‘RT’. Because, it is not adding the values of {STATUS CD} = ‘CP’ and ‘AB’, as I used it already for ‘COMPLETED’ and 'ABANDONED '.

ifelse({STATUS_CD} = ‘CP’, ‘COMPLETED’,

ifelse({STATUS_CD} = ‘AB’, ‘ABANDONED’,

ifelse({STATUS_CD} <> ‘RT’, ‘TRANSFERRED’, ‘Request Failed’
)))

Here my request is, I want to pull all the values (ID’s) under TRANSFERRED status where {STATUS CD} <> ‘RT’, means all the codes include ( ‘CG’,‘AB’,‘CW’,‘CP’,‘CS’ and Null).

Is there any other way of writing this calculated field.

I tried using the below, but no luck.
ifelse(in({SERVICE STATUS CD},[‘CG’,‘AB’,‘CW’,‘CP’,‘CS’]), ‘Request Failed’

And also, I want to Hard code the Request Failed value as ‘Zero’ always in the same calculated field.

Thanks and Appreciate the help!

1 Like

Hi @praveen.gp, to achieve the desired categorization in your calculated and handle the values where {STATUS_CD} <> 'RT', while ensuring that the ‘Request Failed’ status is always zero, you can modify your calculated field as follows:

  1. Create a Calculated Field:
  • Open your dataset in QuickSight.
  • Go to the dataset editor and click on “Add calculated field.”
  1. Use the ifelse function to categorize the statuses:

Here is a revised formula that categorizes the statuses correctly and ensures that ‘Request Failed’ is always zero:

Pseudocode (Syntax may vary)

ifelse(
    {STATUS_CD} = 'RT', 'Request Failed',
    {STATUS_CD} = 'CP', 'COMPLETED',
    {STATUS_CD} = 'AB', 'ABANDONED',
    in({STATUS_CD}, ['CG', 'CW', 'CS', null]), 'TRANSFERRED',
    'TRANSFERRED'  // Default to 'TRANSFERRED' for any other values
)

Explanation:

  • The first ifelse checks if {STATUS_CD} = 'RT' and assigns ‘Request Failed’.
  • The next conditions check for ‘CP’ and ‘AB’, assigning ‘COMPLETED’ and ‘ABANDONED’, respectively.
  • The in function is used to check if {STATUS_CD} is one of ['CG', 'CW', 'CS', null] and assigns ‘TRANSFERRED’.
  • The final ‘TRANSFERRED’ at the end is a catch-all for any other values that might not match the previous conditions.

Hardcode ‘Request Failed’ as Zero:

If you want to ensure ‘Request Failed’ always shows zero, you can handle this at the visualization level by applying a filter or by creating a separate calculated field to override the values during the visualization.

Implementing the Filter at Visualization Level:

  1. Filter for Donut Chart:
  • Create a filter to exclude STATUS_CD = 'RT' from your visualization.
  1. Hardcode ‘Request Failed’ to Zero:
  • Create a calculated field for your measures that sets ‘Request Failed’ to zero:

Pseudocode (Syntax may vary)

ifelse(
    {STATUS_CD} = 'RT', 0,
    sum({your_measure_field})
)

Replace {your_measure_field} with the actual field you are summing or measuring.

By combining these steps, you will ensure that ‘TRANSFERRED’ includes all desired statuses and ‘Request Failed’ is always zero in your visualizations.

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

1 Like

Thanks for the Explanation.
Is it possible to include ‘CP’ and ‘AB’ also in your syntax like below for TRANSFERRED:

in({STATUS_CD}, [‘CP’, ‘AB’,‘CG’, ‘CW’, ‘CS’, null]), ‘TRANSFERRED’

ifelse(
{STATUS_CD} = ‘RT’, ‘Request Failed’,
{STATUS_CD} = ‘CP’, ‘COMPLETED’,
{STATUS_CD} = ‘AB’, ‘ABANDONED’,
in({STATUS_CD}, [‘CP’, ‘AB’,‘CG’, ‘CW’, ‘CS’, null])), ‘TRANSFERRED’,
‘TRANSFERRED’ // Default to ‘TRANSFERRED’ for any other values
)

I tried but no luck. Looking to apply in any other possible way, if there is a chance.

Thanks!