1 Reply Latest reply on Mar 3, 2016 12:59 PM by pooja.gandhi

    Calculated Field - Create field from measure, based on dimension value

    Will Burchard

      Hi,

       

      I'm trying to work out a problem, and after looking at a few other discussions, still can't figure it out.

       

      Here's a data example:

       

      CountyHeader 2Value
      Baker

      2009

      50
      Baker201053
      Baker201157
      Baker201258
      Douglas200978
      Douglas201086
      Douglas201199
      Douglas201297
      Jackson2009121
      Jackson2010134
      Jackson2012133
      Jackson2013142

       

       

      I need a calculated field that does this:

       

      (Baker County 2012 Value)/(Baker County 2009 Value)-1=58/50-1=.16, or 16% growth. It seems like it should be easy, but I keep getting nulls whenever I try it out.

       

      I need to then be able to take this calculated field and use it as the color value for a map (which should be easy once it's calculated).

       

      Any help would be great!

        • 1. Re: Calculated Field - Create field from measure, based on dimension value
          pooja.gandhi

          Hi there!

           

          Which version of Tableau are you using?

           

          If it is Tableau 9.0 or higher, you can utilize LOD expressions:

           

          Baker 2009: {sum(If [County] = 'Baker' and [Header 2] = 2009 then [Value] end )}

          Baker 2012: {sum(If [County] = 'Baker' and [Header 2] = 2012 then [Value] end )}

           

          The calculated field you are looking for:

           

          Calculation1: ([Baker County 2012 Value]/[Baker County 2009 Value]) - 1

           

          The reason you were getting nulls is because without LOD (the use of curly brackets expression), only those rows would get filled where county is baker and year is 2009 or 2012. What you need is all the rows to be filled with the same number, so 50 isn't divided by a null cell which Tableau wouldn't plot. Hope it makes sense:

           

          2 of 2 people found this helpful