Help creating a measure from categorical variable with 3 levels to display as data tip

[MClay]

I have categorical variables for medical conditions and related categorial sub-variables. For example, one categorical medical condition is Cancer, and the sub-vars are Lymphoma, Lung, and Other. All of these vars have Yes/No/Unknown responses in the data. 

I want to display the yes/no/unknown counts for the sub-variables in the datatips so when you mouse over the columns, you'll see the number of people who have the specific sub-condition. I have a bar graph for Cancer (yes/no/unknown), then I calculated the Yes responses for the sub-variables to be able to display them as data tips when mousing over the Yes column for Cancer like so: 

IF ( 'lymphoma'n In ('Yes') )
RETURN 1
ELSE 0

However, this (obviously) doesn't calculate the No/Unknown values and when mousing over those columns, the values are 0 for Lymphoma (frequency). How can I calculate the sub-variables to display the correct counts for the No/Unknown values? TIA! 

[Sam_SAS]

You could use a nested IF statement to also return Unknown as missing. This would give a correct count for 'No' but not Unknown.

If there were only Lymphoma to consider, you could just assign that column to the Group role.

Given that you have 3 types to account for, you could calculate a category that would be something like "Cancer Type" with values (Lymphoma, Lung, Other) and group by that column. In this scenario you could use nested IF statements to get the category values.

The tricky thing is accounting correctly for patients that have multiple types of cancer. I think it should be possible to code some logic in your data preparation to duplicate rows when a patient has multiple types. But the specifics of that are outside my area of expertise.

I hope that will provide some food for thought and help get you started.

Sam

[ThdB]

Consider this example (based on the Class dataset)

Calculated Category Male (if the condition isn't true, set the value to missing)

if 'Sex'n = 'M' return 'Name'n else ''

Aggregated measure: Number of Males (count the distinct number of values and adjust (if necessary with the number of missing values)

Distinct [_ByGroup_] ('Male'n) - (IF ( NumMiss [_ByGroup_] ('Male'n[Raw]) >= 1 )
RETURN 1
ELSE 0 )