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

Q&A
, , ,
1

I am attempting to count results from a data field called “Result Code” in order to create a simple chart to display them. I have created this calculation but it is only partially correct:

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

SMS CONTACT is working as intended.
NO CONTACT is actually the total number of ‘result code = TNS’ minus the total number of ‘result codes = TCS+TFS+THL+TST’ I could use some help on how to write this expression.

2

Hi @Kranard,
I want to make sure I understand the question correctly. In your calculated field, your saying that this part: contains(resultcode, ‘TNS’), ‘NO CONTACT’, is incorrect because it’s supposed to be ‘total number of ‘result code = TNS’ minus the total number of ‘result codes = TCS+TFS+THL+TST’…correct?

Is it possible for a result code to have a TNS value and one of the other values that are directed at SMS CONTACT (TCS, TFS, etc.)?

It may be easier to assist if you were to upload your sheet to the Quick Sight Arena and then I can take a look at how the calculated field can populate.

3

Hi Brett, thanks for responding. The answer to your question is “yes”. All SMS CONTACTS in the data will have a TNS result code (which indicates a message was sent) in addition to the other result codes listed (which are response types). I am looking to subtract the TCS+TFS+THL+TST and what will be left are a count of messages sent but no response was received (there is no specific result code in the data that we can count for this population). The objective is to make a SMS CONTACT/NO CONTACT pie chart to visualize this.

I will watch that video shortly about uploading my sheet.

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 Quick Sight. 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 Quick Sight 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 - Quick Sight Arena)