1 Reply Latest reply on Nov 23, 2016 5:46 AM by lenaic.ridinger

# Show Company Values when Division Values are Null

Hello,

I'm working on Survey data and reporting the percent of Favorable responses ([Response Code] = 4 or 5) by Question.

Users can select a demographic Parameter such as Gender, Race, Highest Degree, etc...

For Gender, survey respondents were asked to choose one for four values; Female, Male, N/A or Gender Nonconforming/Transgender (Trans/GNC).

I am reporting the values side by side for Division (user can select a Division, Department Family or Department Name) and Company.

The issue I am having is when there are no favorable responses to a question for a Gender in a Division, neither the Division reports (this is expected) nor the Company values report (this is unexpected).

For example,

My Results for a particular question

Parameter = Gender

Division

Female     Male       NA     Trans/GNC

43         66           1

54         83           1

79.6%    79.4%     100%

Company

Female     Male        NA     Trans/GNC

5914     2801       155

7827     3530       222

75.6%    79.4%    69.8%

My Results if I split this into two worksheets (1 - Division & 2 - Company) and remove Division, Department Family and Department Name from Filters on the Company worksheet.  I would like these results on one worksheet so I can make a side by side bar graph.

Division

Female     Male         NA     Trans/GNC

Numerator                 43         66          1

Denominator             54         83          1

Percent                 79.6%    79.4%   100%

Company

Female     Male        NA     Trans/GNC

Numerator             5914     2801      155                   63

Denominator          7827     3530      222                 117

Percent                75.6%    79.4%    69.8%          53.8%

Calculations  - Note: [Seq ID] is a respondent

Division - Numerator

If [Response Code] = "4" or [Response Code] = "5"
then

{Fixed
[Question]

,[Seq ID]
,[Response Code]
,[Division]
,[Department Family]
,[Department Name]
,[P.Parameter 1]
:CountD([Seq ID])}

end

Division - Denominator

{Fixed

[Question]

,[Response Code 1-5]

,[Division]

,[Department Family]

,[Department Name]

,[P.Parameter 1]

:CountD([Seq ID])}

Company - Numerator

{Fixed

[Question]

,[Response Code Filter 4-5]

,[P.Parameter 1]

:CountD([Seq ID])}

Company - Denominator

{Fixed

[Question]

,[Response Code 1-5]

,[P.Parameter 1]

:CountD([Seq ID])}

Any suggestions on how I can report ALL Company values on the same worksheet as Division values when a Division Value is Null?

Hillary

• ###### 1. Re: Show Company Values when Division Values are Null

The basic issue is that there doesn't seem to be any data for those combinations of dimensions you are using, and Tableau doesn't make up data when there isn't any. "No data" is different from Null.

However, we can tell Tableau to generate a null when there isn't any data, and that's using the following table calculation: LOOKUP(SUM([Flag]),0).

This table calc returns SUM([Flag]) for the current row, and Null when no value is found. Then you can wrap that in the IIF(ISNULL()) operation to return 0 or a value. alternatively, you could just do the lookup and then Format the calculation to set 0 to be the Text for Special Values.