2 Replies Latest reply on Mar 8, 2013 4:48 PM by Dan Huff

    Help with Calculated Field - Only want to see results greater than zero

    Deborah wilson

      I have tried creating a calculated field to give me the ratios, and while the calculation itself works fine, I am trying to only get the results where production is greater than zero.  I thought that's what the first line would give me, but I keep getting results that are skewing my visuals. 

       

      IF SUM(zn([OIL]*1000))/SUM(zn([GAS]))>0

      THEN SUM(zn([OIL]*1000))/SUM(zn([GAS]))

      ELSE SUM(zn([OIL]))

      END

       

      I tried taking out the "zn" in the first two lines but that did not change the results.  Any assistance is appreciated.

        • 1. Re: Help with Calculated Field - Only want to see results greater than zero
          Deborah wilson

          And the issue is not the value for the production, it's that there are no entries for some of the wells.  Looks like it may involve ISNULL somehow but I'm uncertain at this point and would appreciate some insight.

           

          Thanks,

          • 2. Re: Help with Calculated Field - Only want to see results greater than zero
            Dan Huff

            Deborah--

             

            Have you tried the following:

             

            IF ZN(SUM([OIL]*1000)/SUM([GAS]))>0

            THEN ZN(SUM([OIL]*1000)/SUM([GAS]))

            ELSE zn(SUM([OIL]))

            END

            When you have the ZNs next to the field rather than wrapping the aggregation, we are only going to apply the ZN logic to each row of the dataset. Then, Tableau will try and compute the ratio. Due to the zn being where you had it, it was likely causing some of the denominators to be 0s. This will cause Tableau to return NULL as the value for the ratio.

             

            Wrapping the whole ratio in ZN will allow us to check whether the ratio itself is null. If the ratio returns a Null (due to a missing or 0 denominator) we will catch this with ZN and turn it into zero. In this case, it will not be greater than 0 so we will return zn(sum(Oil)).

             

            Hope this helps,


            Dan