2 Replies Latest reply on Dec 19, 2016 6:55 AM by Tom W

    Calculated field

    Daniel Martins

      Hey guys i am trying to create a calculated formula.

      What i am trying to get is a formula that work like this:

       

      if (Commissionable store) is House then it should take the value in (test drive store)

      but if test drive store is empty then it should take the value from the closest store

      but if closest store is empty then if should tale the value from service center

      if service center is empty then it should take the value that was initially stated in Commissionable store.

      if Commissionable store not house then it should take the value from Commissionable store.

       

      This is my formula:

       

      IF  [Commissionable Store1] ='House'  THEN [Test Drive Store]

      ELSEIF  [Test Drive Store] =NULL THEN[Closest Store]

      ELSEIF [Closest Store]= NULL THEN [Service Center for Pick-up]

      ELSEIF [Service Center for Pick-up]= NULL THEN [Commissionable Store1]

      Else [Commissionable Store1] END

       

      Somehow it stops working after test drive store.... meaning if test drive store is empty it just returns NULL instead of looking for the closest store.

      Can somebody help me?

      best,

      Daniel

        • 1. Re: Calculated field
          Mahfooj Khan

          Hi Daniel,

           

          Use ISNULL() to check any NULL values. Try this

           

          IF  [Commissionable Store1] ='House'  THEN [Test Drive Store]

          ELSEIF  ISNULL([Test Drive Store]) THEN[Closest Store]

          ELSEIF ISNULL([Closest Store]) THEN [Service Center for Pick-up]

          ELSEIF ISNULL([Service Center for Pick-up]) THEN [Commissionable Store1]

          Else [Commissionable Store1] END

           

          Let me know If this help.

           

          Mahfooj

          • 2. Re: Calculated field
            Tom W

            Hi Daniel,

            It would be most effective if you uploaded a Tableau Package Workbook including sample data so we can replicate.

             

            However, I think you are misunderstanding how the IF statement is evaluated. This will be evaluated for each row, but the IF statement will effectively return an answer as soon as the first thing is true. Thus, if the first clause of ' [Commissionable Store1] ='House'" is true, the IF statement will stop evaluating there.

             

            It sounds like you actually need to chain your if statements together more like;

             

            IF [Commissionable Store1] ='House' and zn([Test Drive Store]) >0 then [Test Drive Store]

            ELSEIF [Commissionable Store1] ='House' and zn([Test Drive Store]) = 0 and zn([Closest Store]) > 0 then [Closest Store]

            ELSEIF [Commissionable Store1] ='House' and zn([Test Drive Store]) = 0 and zn([Closest Store]) = 0 then ......