1 Reply Latest reply on Jun 20, 2016 8:07 AM by Michel Caissie

    nested IF creates unexpected results in CASE statement

    Aaron Freed

      bottom line up front is that i have a problem with a calculated field. i'll start with a general/structural overview and then give you the actuals:

       

       

      assume: my.parameter = "B"

       

       

      my calculated field:

      CASE my.parameter

      WHEN "A" THEN X

      WHEN "B" THEN Y

      END

       

       

      this works fine. it works as expected, but it is not what i need. what i need is structured as follows. however, the calculated field above yields a different result than:

       

       

      CASE my.parameter

      WHEN "A" THEN IF condition THEN X END

      WHEN "B" THEN Y

      END

       

       

      even though the parameter value remains unchanged (it remains as "B" in both cases).

       

       

      i have the same problem with an IF statement:

       

       

      IF my.parameter = "A" THEN X

      ELSEIF my.parameter = "B" THEN Y

      END

       

       

      again, it works as expected, but the following yields different and unexpected results:

       

       

      IF my.parameter = "A" THEN IF condition THEN X END

      ELSEIF my.parameter = "B" THEN Y

       

       

      in other words, the calculated field is calculating differently when an IF statement is included in an 'irrelevant' or what should be an 'ignored' line in a CASE statement or nested into an 'irrelevant' IF clause.

       

       

      after running through many permutations, i have isolated the issue to the inclusion of the nested IF statement. whether X is a constant or a LOD expression does not seem to matter...what matters - what breaks things - is if i add an IF to the CASE statement or nest an IF inside of an IF...even if that statement or clause should be disregarded.

       

       

      now that you have a sense of the structure, here are the actual formulas:

       

       

      my.parameter is actually called 'p.rows' and its possible values include "COE" and "state"...

       

       

      the calculated field is called 'x.test revenue' (you can compare it to another calculated field - x.revenue - in the attached workbook) and is calculated as follows:

       

       

      CASE p.rows

      WHEN "COE" THEN IF ([Hits] > 0 OR ISNULL([COE])) THEN {INCLUDE [D-U-N-S Number] : MAX([Revenue (US Dollars, million)])/COUNT([Number of Records])} END

      WHEN "state" THEN {INCLUDE [D-U-N-S Number] : MAX([Revenue (US Dollars, million)])}

      END

       

       

      this expression yields incorrect results. furthermore, it yields different results than the following calculated field, even though (again) the parameter remains set at "state":

       

       

      CASE p.rows

      WHEN "COE" THEN {INCLUDE [D-U-N-S Number] : MAX([Revenue (US Dollars, million)])/COUNT([Number of Records])} END

      WHEN "state" THEN {INCLUDE [D-U-N-S Number] : MAX([Revenue (US Dollars, million)])}

      END

       

       

      the "IF" statement version of the problem looks like this:

       

       

      IF [p.rows] <> "COE" THEN {INCLUDE [D-U-N-S Number] : MAX([Revenue (US Dollars, million)])}

      ELSEIF [p.rows] = "COE" THEN IF ([Hits] > 0 OR ISNULL([COE])) THEN {INCLUDE [D-U-N-S Number] : MAX([Revenue (US Dollars, million)])/COUNT([Number of Records])} END

      END

       

       

      again, without changing the parameter (leaving it at "state"), that formula yields a different result from:

       

       

      IF [p.rows] <> "COE" THEN {INCLUDE [D-U-N-S Number] : MAX([Revenue (US Dollars, million)])}

      ELSEIF [p.rows] = "COE" THEN {INCLUDE [D-U-N-S Number] : MAX([Revenue (US Dollars, million)])/COUNT([Number of Records])}

      END

       

       

      all of the fields referenced are from the original source table, except for [Hits]. [Hits] is introduced through a join, linked through a pivoted field in the source table. link tables through a pivoted field is something that was 'fixed' in tableau 9.3.3, i thought that maybe it could have something to do with that. however, the fact is that ANY sort of conditional breaks these CASE and IF statements. so, it seems unlikely to be related to the joining of tables through a pivoted field. besides, as you can see, it shouldn't matter as the conditional is on an irrelevant line in the CASE statement.

       

       

      can you please help?

        • 1. Re: nested IF creates unexpected results in CASE statement
          Michel Caissie

          Aaron,

           

          Interesting problem,

           

          I mockup something and reproduce the same behavior in a small and less complexe dataset.

          If you  check in the attached,

           

          in calc1

          case [Parameter 3]

          when 'a' then 1

          when 'b' then AVG( [Sales] )

          end

          when selecting b in the parameter I return the AVG Sales, which is 10 and is the expected value

           

          in calc2

          case [Parameter 3]

          when 'a' then 1

          when 'b' then {INCLUDE [Dim B]: AVG( [Sales] )}

          end

          I include the dimension DimB, so for each DimA the SUM of the DimB AVG(Sales) is 20 wich is the expected value.

           

          but in calc3

          case [Parameter 3]

          when 'a' then if isnull([Dim C]) then 1 end

          when 'b' then {INCLUDE [Dim B]: AVG( [Sales] )}

          end

          if I add the DimC  in the condition of the case when a is selected, it changes the value when b is selected.

          Actually it give the same result as if I had included the DimC directly in the case b computation as you can see in calc4.

          case [Parameter 3]

          when 'a' then 1

          when 'b' then {INCLUDE [Dim B],[Dim C]: AVG( [Sales] )}

          end

           

          So the conclusion seems to be that a computation inherits the level of detail of not only the view (dimensions on row shelf, column, color, etc) but also the dimensions added in the Case conditions. We can validate this in calc5

          case [Parameter 3]

          when 'a' then if isnull([Dim C]) then 1 end

          when 'b' then {EXCLUDE [Dim C]: SUM({INCLUDE [Dim B]: AVG( [Sales] )})}

          end

          when in the b computation I exclude the dimension added in the a condition, and get back the expected value.

           

          If not already done, I suggest that you open a case with Tableau support to check if it`s a bug or if this is the normal behavior, because it is very confusing and error prone.

           

          And in the mean time, you maybe able to resolve your problem by excluding the dimensions that were added in the other conditions of your case.

           

           

          Michel