5 Replies Latest reply on Sep 11, 2018 9:39 AM by Joe Oppelt

    Difference code

    Priscilla Garza

      So, I'm trying to make a difference column. If either of my 2 columns that I'm pulling from have a zero then I want the difference column to null it out/show nothing. (Although if it could say "N.A." that would be way better.) If both columns have numerical data, then I want them to subtract for the difference...

       

      This is what I have at this point:

       

      If [2017]=0 then null

      ElseIf [2018]=0 then null

      Else [2017]-[2018]

      End

       

      So far, if I just do whats below, then it will work. But if I try to add both conditions, then it gets lost.

       

      If [2017] (or 2018) = 0 Then Null

      Else [2017]-[2018]

      End

       

      Im new to Tableau so I'm not sure on the specifics of the code as they seem to be a little finicky.

        • 1. Re: Difference code
          Joe Oppelt

          This should work:

           

          If [2017]=0 then null

          ElseIf [2018]=0 then null

          Else [2017]-[2018]

          End

           

          just like you typed it.


          Otherwise do:

           

          If [2017]=0 or [2018]=0 then null

          Else [2017]-[2018]

          End

           

          If you want "N.A" then you'll have to convert the output to string.  If your prior calc was called [Year Calc] then do this:

           

          if isnull([Year Calc]) then "N.A" else STR([Year Calc]) END

           

          I suspect you might need to upload a sample workbook if this doesn't get you there.  Do you know how to make a packaged workbook?

          • 2. Re: Difference code
            Priscilla Garza

            Ive tried it both ways and it still doesn't work for some reason. This is a test sample I came up with as it still doesn't work even with this data in Tableau. To get the separate 2018/2017 columns, make a calculated field each with the code I have below.

             

            1. 2018 = ZN(if [Term]=2018 then [Count] END)

            2. 2017 = ZN(if [Term]=2017 then [Count] END)

             

            Then try making the difference column with the code we discussed. I don't get if I'm missing something or what is going on. Hopefully you can figure it out!

             

            Thanks!

            • 3. Re: Difference code
              Joe Oppelt

              See attached,  (I made this in V9.2.  I hope you're not on something older than that.)

               

              Sheet 1 just has a count of Majors by year.

               

              Sheet 2 has counts by year for each major.

               

              Sheet 3.  I created a calc called a FIXED LOD that grabs the value of COUNT for each Major if the year was 2017.  This is a calc that gets added to each row, regardless of what year that row actually is.  So you can see that for 2017 the number is the duplicate of the COUNT value, and in 2018 the same 2017 number is displayed, even though the year is 2018.


              Sheet 4 adds two more calcs.  One is the numerical DIFF.  The other is a string representation of the diff, shoving N.A where you have a null.

              • 4. Re: Difference code
                Priscilla Garza

                Is there any way I can make the string diff as its own column? Such as how I have drawn it in the picture? As I need it to be out to the side for the report I'm doing and not underneath.

                • 5. Re: Difference code
                  Joe Oppelt

                  See Sheet 5 in the attached.

                  1 of 1 people found this helpful