3 Replies Latest reply on Jan 12, 2017 1:45 PM by Jason Mathew

    Industry Classification using IF THEN statements

    Jason Mathew

      Hi Friends,

       

      I've created a simple industry classification parameter for Company names, using IF THEN statements. It works perfectly, but I don't know what to do in situations where a single organization falls under two unique industries. For example, CA Inc. can fall under both "Computer Software" and "Computer Hardware." I can't logically compare two strings with AND, so I'm not sure how to proceed.

       

      Does anyone have any insight here? Please let me know..

       

      IF [Member] ="3M" THEN "Chemicals"

      ELSEIF [Member] ="AARP" THEN "Membership Organizations"

      ELSEIF [Member] ="BNY Mellon" THEN "Banking"

      ELSEIF [Member] ="CA Inc" THEN "Computer Software"

      ELSEIF [Member] ="CH2M Hill" THEN "Construction"

      END

        • 1. Re: Industry Classification using IF THEN statements
          Steve Mayer

          There are many ways to tackle this, but I'll offer two possible solutions - one sort of "quick & dirty", and the 2nd a little more complex.

           

          First possible solution:

           

          Step 1

          Modify your case statement to include a list of classifications for each Company. So for CA Inc, you would have:

           

          ELSEIF [Member] ="CA Inc" THEN "Computer Software, Computer Hardware"

           

          Step 2

          For filtering, you would create a Parameter called "Selected Classification" and you would add all of the possible classifications as Strings to the parameter, e.g.

           

          Chemicals

          Computer Software

          Computer Hardware

          etc

           

          Step 3

          Create a calculated field called "Matches Classification" (assume that the original calculated field is called [Classification]), with the formula:

           

          CONTAINS([Classification], [Selected Classification])

           

          This will return true if the Classification contains the substring Selected Classification - so for example, CA Inc would return True for BOTH "Computer Hardware" and "Computer Software"

           

          Step 4

          Add this calculated field as a filter - you should be able to select either Computer Hardware or Computer Software, and see CA Inc.

           

          The other possible solution is handle multiple classifications in the data, such that you would have multiple records for a company that belonged to more than one classification. You could do this for example using data blending, with a secondary data source that had a map of classifications for all companies. You would blend your primary data source with this data source. It would look something like this:

           

          Company Classification

          CA Inc     Computer Hardware

          CA Inc     Computer Software

          Dow     Chemicals

          etc.

          etc.

           

          You would blend your primary source to this source on company name, and then filter using the Classification field.

           

          Hope this helps,

           

          -Steve

          2 of 2 people found this helpful
          • 2. Re: Industry Classification using IF THEN statements
            Jason Mathew

            Thank you Steve! The first solution worked perfectly. As I am looking to apply a parameter across multiple sheets, I wouldn't be able to include a grouped field in the calculation engine, so Solution 2 is out. Thank you again!

            • 3. Re: Industry Classification using IF THEN statements
              Jason Mathew

              Hi Steve,

               

              I hope you're still active on here, because I have another question Based on your response above (and let me know if you'd like me to refresh you on the problem statement), but what do I do if I want a Selected Parameter to consider ALL industries? For example, once I add the True or False field to a workbook, and mark it as 'True,' it considers which ever Industry I've chosen. If I select 'False' however, it considers all Industries but still ignores which ever 'Industry' I chose.

               

              Does this make sense? Essentially, I want my parameter options to include an 'All' option which allows me to show multiple views in a Dashboard.