5 Replies Latest reply on May 23, 2016 1:36 PM by Tom W

    Combining If Then statements without losing place in categories

    john.stjohn
    john.stjohn May 23, 2016 12:24 PM

      Hello:

       

      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

      • 117 Views
      • Tags:
        • 1. Re: Combining If Then statements without losing place in categories
          Tom W
          Tom W Ambassador May 23, 2016 12:34 PM (in response to john.stjohn)

          It's hard to follow your example without sample data. Please prepare a sample and attach a Tableau Packaged Workbook.

           

          To me it seems like your 'single dimension' isn't possible. Is a single record satisfies your first criteria of '[Percent of Market] >= [Select Comp Ratio]' then no following criteria will be evaluated. A calculated field will only return one result. Is it not possible for someone to meet that criteria and also have no direct reports?

           

          • 2. Re: Combining If Then statements without losing place in categories
            john.stjohn
            john.stjohn May 23, 2016 12:45 PM (in response to Tom W)

            Tom:

             

            You're hitting on my exact problem.   The data is enormous and I can put together a dummy table but you have hit the nail on the head.  We have thousands of people who fall into multiple categories of what would be called outliers.   It is not only possible but highly likely that someone would be a salary outlier, a 5 or more level gap, a manager with no direct reports or some other combination.

             

            I've tried so many and, or, case, and if then statements but I can't ever get the sums to come out of the data.  The desire is also to use parameters on the list of outliers to allow viewers to select what the ranges are that define the outliers so I'm not able to just group the data with a sql statement.   I really want to count all the salary outliers in a register, then not leaving any of them out, count all the functional outliers, and move down the list. 

             

            I wasn't sure if it was possible or not.  

             

             

            It will take me a bit of work to get the data into a format and a packaged workbook.  This is coming our of our hadoop server and has about 1000 fields wide and nearly 14 million records and there are 436 calculated fields that tie to it so I'll have to work a bit to pare it down with a smaller data set and dimensions that don't lose the calcs for the work the dashboards does.  Let me see what I can do.  Thank you.

             

            jsj

            • 3. Re: Combining If Then statements without losing place in categories
              Tom W
              Tom W Ambassador May 23, 2016 1:03 PM (in response to john.stjohn)

              John,

              Don't focus too specifically on your actual dataset. Mock something up in Excel. The more contained you can make it, the easier it is for us to understand.

               

              However, I think quite simply the answer is that one calculated field = one result. You cannot return multiple categories within the same calculated field. Thus, you'll need to break it out into multiple calculated fields to ensure the results are more focused.

               

              Speaking very generally, let's say that Mickey Mouse has the following exceptions;

              • Salary is above average salary for role
              • Salary is above average salary for region
              • Direct Reports is fewer than 5
              • Manager with no direct reports
              • Tenure is below average for region

               

               

              If you create a calculated field called 'Exception' with all your logic, you'll only return the first true exception which is the problem. Thus, If you need to know each exception specifically, you would need one calculated field per exception;

                • I.e. exSalaryAvgRole = IF [Salary] > [SalaryRoleAverage] then 1 else 0 end
                • using this method you could identify the specific exceptions Mickey has generated, or get a count of the number of exceptions mickey has generated i.e. SUM(exSalaryAvgRole + exSalaryAvgRegion + ......)

               

              If the lowest level of detail isn't important, you could aggregate these together into counts. I.e. You  might want a count of salary exceptions for Mickey, you could just do;
              SalaryExceptionCount =
              SUM(
              IF [Salary] > [SalaryroleAverage] THEN 1 ELSE 0 END +

              IF [Salary] > [SalaryRegionAverage] THEN 1 else 0 END +

              ........... other salary exception rules here

              )

              • 4. Re: Combining If Then statements without losing place in categories
                john.stjohn
                john.stjohn May 23, 2016 1:11 PM (in response to Tom W)

                OK, so what I thought was true, there wouldn't be a way to plot in the data in a line graph over time:

                 

                for a given actor in a character suit:

                 

                Months over time

                 

                Bob wore a mickey suit month 1, a mickey and donald suit month two, and a goofy suit month three

                 

                If the data set simply had instances of actor, date and  different columns for whether or not they wore a specific costume.

                 

                I guess I'll just have to have multiple plots for the fields in the calculations.

                 

                I thought that was the case but hoped there was some trick of case statements that would let me carry over the next if then

                • 5. Re: Combining If Then statements without losing place in categories
                  Tom W
                  Tom W Ambassador May 23, 2016 1:36 PM (in response to john.stjohn)

                  If you have a time dimension in your data then you could totally plot someones status of time. Basically for each 'point in time' your evaluating for, you would need all the necessary data to make the evaluation and thus, plot it. You can potentially 'carry' something over but it would really depend on the specific structure of your data.

                   

                  Throw up an example and i'll take a look.