Combining If Then statements without losing place in categories
john.stjohn May 23, 2016 12:24 PMHello:
I'm using Tableau in a fairly big dashboard with our overall HR data. Within the data I have a set of outliers that can be calculated for any team member:
For example, a salary outlier would be someone with a higher compensation ratio than the normal job that they have would be paid for a given location.
A manager with no direct reports (not managing anyone) would be someone who has zero direct reports.
I need to count these anomalies which I can do just fine. But I would really prefer to build a single dimension that categorizes all of the anomalies:
I wrote this which works fine:
if [Percent of Market] >= [Select Comp Ratio]then 'Salary'
elseif [Best Cost] ='Rest' then 'Shoring'
elseif [Functional Outlier] = '1' then 'Functional'
elseif [Numeric Value for Career Level Calc] = 0 then 'Same Level Reporting'
elseif [Numeric Value for Career Level Calc] >=5 then '5 or more Level Gap'
elseif [Numeric Value for Career Level Calc] <=0 then 'Negative Level Reporting'
elseif [Direct Reports] = 0 and [Manager Flag] = 'Y' then 'Manager with No Direct Report'
elseif [Direct Reports] = 1 then '5 or Fewer Direct Reports'
elseif [Direct Reports] = 2 then '5 or Fewer Direct Reports'
elseif [Direct Reports] = 3 then '5 or Fewer Direct Reports'
elseif [Direct Reports] = 4 then '5 or Fewer Direct Reports'
elseif [Direct Reports] = 5 then '5 or Fewer Direct Reports'
else null end
The result is a table that accurately figures up each outlier but only the first time the outlier shows up as a record.
If I limit a function like this:
if [Percent of Market] >= [Select Comp Ratio]then 'Salary'
else null end
Then I get all of the comp ratios and then if I build a second calc like this:
if [Numeric Value for Career Level Calc] = 0 then 'Same Level Reporting'
else null end
then I get the same value as the first table for comp ratio outliers but a higher value for same level reporting because the comp ratio people are not double counted.
I really want an "and" statement. The reason is that I would like to plot the anomalies by month as shown in the attachment.
I want to get a suggestion for either replacing the nested if then statement with somethign that sums iteratively or figure out a different way to combine the categorizations into a single dimension.
Thanks for any advice.
jsj
-
Anomalies Overview.jpg 167.9 KB