Ifelse question where results are a sub-set of other results

Question & Answer
, ,
4

Hi @kranard to achieve the calculation where “NO CONTACT” is the total number of ‘result code = TNS’ minus the total number of ‘result codes = TCS+TFS+THL+TST’, you can use a calculated field with conditional logic. Here’s a pseudocode high level approach:

  1. Create a Calculated Field for SMS CONTACT: This will help us count the number of result codes for TCS, TFS, THL, and TST.

Pseudocode (Syntax may vary)

ifelse(
    contains(resultcode, 'TCS') or contains(resultcode, 'TFS') or contains(resultcode, 'THL') or contains(resultcode, 'TST'), 'SMS CONTACT',
    'OTHER'
)
  1. Create a Calculated Field for NO CONTACT: This will initially mark the TNS codes.

Pseudocode (Syntax may vary)

ifelse(
    contains(resultcode, 'TNS'), 'NO CONTACT',
    'OTHER'
)
  1. Calculate the Number of SMS CONTACT and NO CONTACT: Use the sum function to count the occurrences.
  • For SMS CONTACT:

Pseudocode (Syntax may vary)

sum(ifelse(
    contains(resultcode, 'TCS') or contains(resultcode, 'TFS') or contains(resultcode, 'THL') or contains(resultcode, 'TST'), 1, 0))
  • For NO CONTACT:

Pseudocode (Syntax may vary)

sum(ifelse(contains(resultcode, 'TNS'), 1, 0))
  1. Subtract the SMS CONTACT Count from NO CONTACT Count: Finally, create a calculation to subtract the count of SMS CONTACT from NO CONTACT.

Pseudocode (Syntax may vary)

sum(ifelse(contains(resultcode, 'TNS'), 1, 0)) - sum(ifelse(
    contains(resultcode, 'TCS') or contains(resultcode, 'TFS') or contains(resultcode, 'THL') or contains(resultcode, 'TST'), 1, 0))
  1. Combine into a Single Field (optional, if you want a single calculated field to display):

Pseudocode (Syntax may vary)

ifelse(
    contains(resultcode, 'TCS') or contains(resultcode, 'TFS') or contains(resultcode, 'THL') or contains(resultcode, 'TST'), 'SMS CONTACT',
    contains(resultcode, 'TNS'), 'NO CONTACT',
    'NA'
)

Then create a new calculated field for the subtraction logic separately.

  1. Create Visuals: Use the calculated fields to create your visuals in QuickSight. For example, you can use a bar chart to display the counts of “SMS CONTACT” and the adjusted “NO CONTACT”.

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!

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)