6 Replies Latest reply on Aug 25, 2013 2:46 PM by Aaron Clancy

    IF Statement with Mutiple AND Conditions

    jason ricapito

      Thanks in advance for any help. 

       

      The below calculation is using an IF statement where my objective is to return a value of 'Y' when two, two part, conditions are met. Both two part conditions are calculating a percentage of one member of the same dimension. 

       

      The below is a valid calculation but doesn't work.   It does work when I only use one two part condition.  I figure I'm missing something simple.  I tried encapsulating both conditions with parentheses and that didn't work.

       

      IF

      (ATTR([STATUS]) = 'APP' AND SUM([SUBMITTED_AMT]) / TOTAL(SUM([SUBMITTED_AMT])) < .1)

      AND

      (ATTR([STATUS]) = 'ACT' AND SUM([SUBMITTED_AMT]) / TOTAL(SUM([SUBMITTED_AMT])) < .1)

      THEN 'Y' 

      ELSE 'N'

      END

        • 1. Re: IF Statement with Mutiple AND Conditions
          Aaron Clancy

          Just realized what you were asking.  Disregard my previous response.

           

          ATTR([STATUS]) can't be two different things at the same time in a calc.

           

          i.e. can't be 'APP' as well as 'ACT'

          • 2. Re: IF Statement with Mutiple AND Conditions
            Joshua Milligan

            Jason,

             

            There are 3 types of calculations in Tableau. 

            1. Row Level which does a calculation for each row of data.

            2. Aggregate which performs aggregate functions such as SUM, MIN, MAX, AVG, etc... which are performed as part of the query to the data source.

            3. Table Calculations which perform calculations on the table of data that is the result of the initial query formed by fields used on various shelves such as Filters, Rows, Columns, and Marks.

             

            You are using aggregate calculations and TOTAL which is a table calc.  At the aggregate level ATTR() will return the single value of a dimension at the level of detail present in a view or "*" if there are more than one values present for the level of detail.

             

            So, there is no way you'll ever get ATTR(Dimension) == "A" AND ATTR(Dimension) == "B" because if A and B are present values then ATTR(Dimension) will equal "*".

             

            What you might try to do is to create an initial Row Level calculation that groups "APP" and "ACT".  For example:

             

            IF [STATUS] == "APP" OR [STATUS] == "ACT" THEN "APP or ACT" END

             

            Then use that calculation in additional calculated field(s).  But the way those calculations will work will depend somewhat on the view you create.  If you share a packaged workbook (with dummy data), I'd be happy to take a look and see what the specific solution would be.

             

            Regards,

            Joshua

            • 3. Re: IF Statement with Mutiple AND Conditions
              Joshua Milligan

              Aaron,

               

              The issue isn't that the calculation is invalid syntactically or that there is any mismatch of field types or aggregation.  Instead, it logically can never be true.  Note below the results of ATTR(Category) depending on how many values of Category are present in the level of detail defined in the view.  When there is only one (every row on Sheet 1 or Sheet 2 for Furniture) then ATTR() will evaluated to the single value present.  But when there is more than one value, you will get a "*" (Sheet 2, except Furniture).

               

              So

              ATTR(Category) == "Labels" AND ATTR(Category) == "Paper"

              can never be true.

               

               

              Regards,

              Joshua

              ATTR.png

              • 4. Re: IF Statement with Mutiple AND Conditions
                Aaron Clancy

                Yeah the second I realized that I misread his issue I updated my response to address what the actual issue was.  Although, you went in to much more detail and I'm sure Jason will appreciate that :-)

                • 5. Re: IF Statement with Mutiple AND Conditions
                  Joshua Milligan

                  Aaron,

                   

                  I can't tell you how many times I've done the same thing.  I just noticed we're neighbors!  I'm in DFW.

                   

                  Regards,

                  Joshua

                  • 6. Re: IF Statement with Mutiple AND Conditions
                    Aaron Clancy

                    I'm from Irving, TX.  Small world lol.  I'll stop spamming the post now and I'm sure I'll see you on other responses in the future lol.  I'll be at TCC13 as well.