Spotfire Bin or Calculated Column

Question

I hope you can help. The issue I am facing is this. I have a column in my data called 'Customer Subtype' In this column I have numerous values for the same thing. So Dentist, Pharmacist, Doctor, Dietitian etc all come under the same thing HCP (Health Care Professional) What I want to do is take all the values I deem to be HCPs and differentiate them from values I think are HCO's (Health Care Organisations)

I have tried to BIN my HCP values as you can see in Pic 1

And I have applied this HCP binned column to a text area on the left of my Map chart and bar chart See Pic 2

Pic 1

Pic 2

In Pic 2 you can see I have filtered for 2017, Bulgaria and HCP's and I get the appropriate response from the chart. But the other HCO's are still showing and this is not exactly what I want. What I want Ideally is for the HCP's and HCO's to be a check box with the amount of HCP's or HCO's as in the number of HCP's or HCO's that has accumulated this amount of money underneath each check box, so that a user can un-check or check, and tell the amount of each, (see artistic representation Pic 4) ;-) and of course again the Map will react accordingly.

Can binning achieve this or should a Calculated column with something like If 'Customer Subtype' = "Dentist" or "Doctor" or "Pharmacist" then count and show me the value. be more appropriate in this scenario.

Pic 4

Scsimnon Code

case  when ([Customer Subtype]="Administrator") or (
          [Customer Subtype]="Allied Health Practitioner") or (
[Customer Subtype]="Dental Hygienist/Assistant") or (
[Customer Subtype]="Dentist") or (
[Customer Subtype]="Dietitian") or (
[Customer Subtype]="Educator") or (
[Customer Subtype]="Executive") or (
[Customer Subtype]="General Practitioner") or (
[Customer Subtype]="Health Educator") or (
[Customer Subtype]="Healthcare Business Professional Other") or (
[Customer Subtype]="Healthcare Professional Other") or (
[Customer Subtype]="Healthcare Professional Unknown") or (
[Customer Subtype]="Hospital Pharmacist") or (
[Customer Subtype]="Hospital Physician") or (
[Customer Subtype]="Hospital/Practice Physician") or (
[Customer Subtype]="Journalist") or (
[Customer Subtype]="Lab Technician") or (
[Customer Subtype]="Medical Assistant") or (
[Customer Subtype]="Medical Student") or (
[Customer Subtype]="Microbiologist") or (
[Customer Subtype]="Midwife") or (
[Customer Subtype]="Non-Medical Healthcare Worker") or (
[Customer Subtype]="Nurse") or (
[Customer Subtype]="Nurse Practitioner") or (
[Customer Subtype]="Occupational Therapist") or (
[Customer Subtype]="Office Manager") or (
[Customer Subtype]="Paramedical") or (
[Customer Subtype]="Pharmacist Assistant") or (
[Customer Subtype]="Physical Therapist") or (
[Customer Subtype]="Physician") or (
[Customer Subtype]="Prescribing Nurse") or (
[Customer Subtype]="Researcher") or (
[Customer Subtype]="Resident") or (
[Customer Subtype]="Social Worker") or (
[Customer Subtype]="Specialist Physician") or (
[Customer Subtype]="Veterinarian") or (
[Customer Subtype]="Pharmacist") then "HCP's" 

when ([Customer Subtype]="Advisory Board/Committee Public Service") or (

[Customer Subtype]="Assoc/Society/Foundtn") or (
[Customer Subtype]="Business/Employers") or (
[Customer Subtype]="Cancer Treatment Center") or (
[Customer Subtype]="Chain Retail Pharmacy") or (
[Customer Subtype]="City Health Department Public Service") or (
[Customer Subtype]="City Hospital Committee Miscellaneous") or (
[Customer Subtype]="City Hospital Department") or (
[Customer Subtype]="City University/College Miscellaneous") or (
[Customer Subtype]="City/County Health Authorities Public Service") or (
[Customer Subtype]="City/County Primary Care Health Authority Public Service") or (
[Customer Subtype]="City/County Secondary Care Health Authority Public Service") or (
[Customer Subtype]="County Assoc/Society/Foundtn Miscellaneous") or (
[Customer Subtype]="County Health Department Public Service") or (
[Customer Subtype]="County Healthcare Fclty Contracting Agent") or (
[Customer Subtype]="County Hospital") or (
[Customer Subtype]="County Medical Ofce O/P Medical Fclty") or (
[Customer Subtype]="County Miscellaneous") or (
[Customer Subtype]="Diagnostic Center ") or (
[Customer Subtype]="Disease Management Team") or (
[Customer Subtype]="Distributor") or (
[Customer Subtype]="Dual/Oth Gov Agcy Hospital") or (
[Customer Subtype]="Dual/Oth Gov Agcy Miscellaneous") or (
[Customer Subtype]="Family Planning Clinic") or (
[Customer Subtype]="Federal/Country Assoc/Society/Foundtn Miscellaneous") or (
[Customer Subtype]="Federal/Country Employer Health Plan Business") or (
[Customer Subtype]="Federal/Country Govt Ministry Miscellaneous") or (
[Customer Subtype]="Federal/Country Health Department Public Service") or (
[Customer Subtype]="Federal/Country Hospital") or (
[Customer Subtype]="Federal/Country Hospital Department") or (
[Customer Subtype]="Formulary Committee") or (
[Customer Subtype]="General Hospital") or (
[Customer Subtype]="Formulary Committee") or (
[Customer Subtype]="Health Authorities Public Service") or (
[Customer Subtype]="Health Network") or (
[Customer Subtype]="Health Network System Manage Care Org") or (
[Customer Subtype]="Hospital") or (
[Customer Subtype]="Hospital Department") or (
[Customer Subtype]="Hospital District Hospital") or (
[Customer Subtype]="Hospital District Physician Medical Practice O/P Medical Fclty") or (
[Customer Subtype]="Hospital I/P Pharmacy") or (
[Customer Subtype]="Insurance Managed Care Org") or (
[Customer Subtype]="Long Term Care") or (
[Customer Subtype]="Long-Term Care Provider") or (
[Customer Subtype]="Managed Care Org") or (
[Customer Subtype]="Military Hospital") or (
[Customer Subtype]="Miscellaneous") or (
[Customer Subtype]="Multi-Disciplinary Health") or (
[Customer Subtype]="Non-Instit Hlth Care Svc") or (
[Customer Subtype]="Non-Profit Assoc/Society/Foundtn Miscellaneous") or (
[Customer Subtype]="Non-Profit Charitable Org/Church Miscellaneous") or (
[Customer Subtype]="Non-Profit Indepndnt Physician Assoc Business") or (
[Customer Subtype]="Non-Profit Patient Advocacy Miscellaneous") or (
[Customer Subtype]="Nursing/Rest Home Long-Term Care") or (
[Customer Subtype]="O/P Medical Fclty") or (
[Customer Subtype]="O/P Medical Fclty Department") or (
[Customer Subtype]="Pharmacy") or (
[Customer Subtype]="Pharmacy Department") or (
[Customer Subtype]="Private Ambulatory Surgery Center O/P Medical Fclty") or (
[Customer Subtype]="Private Assoc/Society/Foundtn Miscellaneous") or (
[Customer Subtype]="Private Business") or (
[Customer Subtype]="Private Clinic O/P Medical Fclty") or (
[Customer Subtype]="Private Diagnostic Center Miscellaneous") or (
[Customer Subtype]="Private Hospital") or (
[Customer Subtype]="Private Hospital Intensive Care Unit") or (
[Customer Subtype]="Private Hospital Owned O/P Medical Fclty") or (
[Customer Subtype]="Private Hydrotherapy O/P Medical Fclty") or (
[Customer Subtype]="Private Indepndnt Physician Assoc Business") or (
[Customer Subtype]="Private Media Miscellaneous") or (
[Customer Subtype]="Private Medical Ofce O/P Medical Fclty") or (
[Customer Subtype]="Private Miscellaneous") or (
[Customer Subtype]="Private Nursing/Rest Home Long-Term Care") or (
[Customer Subtype]="Private Patient Advocacy Miscellaneous") or (
[Customer Subtype]="Private Physician Medical Practice O/P Medical Fclty") or (
[Customer Subtype]="Private Third Party Administrator Business") or (
[Customer Subtype]="Private University/College Miscellaneous") or (
[Customer Subtype]="Private Vendor Miscellaneous") or (
[Customer Subtype]="Private Wholesaler Distributor") or (
[Customer Subtype]="Public Ambulatory Surgery Center O/P Medical Fclty") or (
[Customer Subtype]="Public Assoc/Society/Foundtn Miscellaneous") or (
[Customer Subtype]="Public Business") or (
[Customer Subtype]="Public Charitable Org/Church Miscellaneous") or (
[Customer Subtype]="Public Children's & Social Srvcs Public Service") or (
[Customer Subtype]="Public Clinic O/P Medical Fclty") or (
[Customer Subtype]="Public Formulary Committee Miscellaneous") or (
[Customer Subtype]="Public General Hospital") or (
[Customer Subtype]="Public GPO Managed Care Org") or (
[Customer Subtype]="Public Home Health Non-Instit Hlth Care Svc") or (
[Customer Subtype]="Public Hospital") or (
[Customer Subtype]="Public Hospital Department") or (
[Customer Subtype]="Public Hospital I/P Pharmacy") or (
[Customer Subtype]="Public Hospital Owned O/P Medical Fclty") or (
[Customer Subtype]="Public Hospital Special Treatment Fclty/Acute Care") or (
[Customer Subtype]="Public Hospital Special Treatment Fclty/Non-Acute Care") or (
[Customer Subtype]="Public Medical Group Management Business") or (
[Customer Subtype]="Public Medical Ofce O/P Medical Fclty") or (
[Customer Subtype]="Public Medical School Miscellaneous") or (
[Customer Subtype]="Public Mental Health Center O/P Medical Fclty") or (
[Customer Subtype]="Public Miscellaneous") or (
[Customer Subtype]="Public Nursing/Rest Home Long-Term Care") or (
[Customer Subtype]="Public Patient Advocacy Miscellaneous") or (
[Customer Subtype]="Public Physician Medical Practice O/P Medical Fclty") or (
[Customer Subtype]="Public Police/Fire/Ambulance Public Service") or (
[Customer Subtype]="Public Provider Long-Term Care") or (
[Customer Subtype]="Public Retail Pharmacy") or (
[Customer Subtype]="Public Service") or (
[Customer Subtype]="Public University/College Miscellaneous") or (
[Customer Subtype]="Public Vaccine Service Provider O/P Medical Fclty") or (
[Customer Subtype]="Research/Training Hospital") or (
[Customer Subtype]="Specialty Hospital") or (
[Customer Subtype]="State/Region Assoc/Society/Foundtn Miscellaneous") or (
[Customer Subtype]="State/Region Children's & Social Srvcs Public Service") or (
[Customer Subtype]="State/Region Family Planning O/P Medical Fclty") or (
[Customer Subtype]="State/Region Health Authorities Public Service") or (
[Customer Subtype]="State/Region Health Care Management Group Public Service") or (
[Customer Subtype]="State/Region Health Care Management Group Public Service") or (
[Customer Subtype]="State/Region Health Department Public Service") or (
[Customer Subtype]="State/Region Healthcare Fclty Contracting Agent") or (
[Customer Subtype]="State/Region Hospital") or (
[Customer Subtype]="State/Region Hospital Committee Miscellaneous") or (
[Customer Subtype]="State/Region Pharmacy Board Miscellaneous") or (
[Customer Subtype]="University Hospital") or (
[Customer Subtype]="University/College Miscellaneous") or (
[Customer Subtype]="VA Miscellaneous") or (
 [Customer Subtype]="Army Hospital") then "HCO's"
    else [Customer Subtype]
end as [New Column]

Pic 5

Philips Attempt at HCP and HCO [Organization Name]

case  when [Organization Name]="" then "HCP" 

when [Organization Name]<>"" then "HCO"


end as [New Column]

Pic 6

Answer 1

Based on what you have stated in your post, a calculated column would definitely benefit you in the long run. Something like:

case when [Customer Subtype] = "Dentist" or
          [Customer Subtype] = "Doctor" or
          [Customer Subtype] = "Pharmacist" then "HCP"
    when  [Customer Subtype] = "something" or
          [Customer Subtype] = "something else" then "HCO"
    else  [Customer Subtype]
end as [New Column]

However, if you are wanting to apply a check box filter, i'd leave out the ELSE above. Then, for your counts, you'd just add a Dynamic Item:

• Right CLick on Text Area > Edit Text Area
• Insert Dynamic Item > Calculated Value
• Under the Data tab, select the appropriate data table
• Under the Values tab, change the Calculate values using: section to the formula below

Formulas:

If([New Column] = "HCP",UniqueCount([Customer Subtype]))

Similarly, for your HCO...

If([New Column] = "HCO",UniqueCount([Customer Subtype]))