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:
- Create a Calculated Field:
- Open your dataset in QuickSight.
- Go to the dataset editor and click on “Add calculated field.”
- 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:
- Filter for Donut Chart:
- Create a filter to exclude
STATUS_CD = 'RT' from your visualization.
- 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!