6 Replies Latest reply on Jul 11, 2016 8:41 AM by Dave Eckholm

    Is it possible to create a calculated field across rows and columns

    Dave Eckholm

      Is there a way to calculate the difference between one row up and one column over - as shown in the yellow highlight?

       

        • 1. Re: Is it possible to create a calculated field across rows and columns
          Joe Oppelt

          You can do that.

           

          If you had a calc that looked something like this:

           

           

          IF [Src Yr]=2015 and [Fiscal Yr Nbr] = 2015 THEN SUM([whatever]) ELSE null END

           

          (Or maybe ... ELSE 0 END).

           

          You'll have to play with it.  Throw the calc on the text shelf and if you do it correctly you'll see zeros everywhere except for that one cell.

           

          And you might have to it this way:

           

          SUM( IF [Src Yr]=2015 and [Fiscal Yr Nbr] = 2015 THEN [whatever] else 0 END )

           

          And you gave only two examples of what you're trying to mess with.  It almost looks like you want to do this:

           

          IF [Src Yr] = [Fiscal Yr Nbr]  THEN SUM([whatever]) ELSE null END

           

          EDIT NOTE:   ^^ I edited this. ^^  The previous syntax didn't make any sense.  ^^

           

          If you provided a sample workbook I could play around with it.  Or maybe that gets you going.

          • 2. Re: Is it possible to create a calculated field across rows and columns
            Dave Eckholm

            Hi Joe,

            Thanks for the quick response. I'm looking to have this calculation apply to all values - so not only am I looking to calculate what I had highlighted I also would like to determine the difference for all cells compared to one row up and one column to the left. I was thinking I need to understand how far from the first/last value each cell is vertically and also horizontally and use that combination - but that is as far as I've gotten.

             

            Thanks - have a great weekend

            Dave

            • 3. Re: Is it possible to create a calculated field across rows and columns
              Joe Oppelt

              Ah.

               

               

              Consider the LOOKUP function.  You can tell Tableau to look up something "over there".

               

              See attached.  (Version 9 workbook will upgrade to any version you are on.)

               

              So what I did in here is this:

               

              I made an LOD to see how many columns we have.

               

              I created index calcs to help me know if I am on an outer edge of the grid (upper or left edge).  One does table down.  One does table across.  If I'm on the edge, there is no diagonal for me to get.

               

              Then I did LOOKUP to look back the number of "hops" there are in the columns, plus one.  And I told Tableau to do that LOOKUP calc "Across then down".

               

              And just so you can see how I made the data, I included the excel sheet I used.

               

              See if you can translate this to your workbook.  If you need help, upload a packaged workbook and I'll help you.

              • 4. Re: Is it possible to create a calculated field across rows and columns
                Dave Eckholm

                Thanks Joe - this is exactly what I needed - can you explain  this statement -  LOOKUP( sum([Data]),-(attr([number of Dim1])+1) )

                 

                I'm not understanding how it stops at the previous row

                 

                Dave

                • 5. Re: Is it possible to create a calculated field across rows and columns
                  Joe Oppelt

                  See attached.

                   

                  I added another version of the index calc.  [Index wrap].  This lets you see what Tableau is doing under the hood.  You don't need it for anything else but demonstration in this example.

                   

                  If you edit table calc on each of the various index calcs, you'll see that each one "walks" the table differently.  One looks down.  one looks across.  One looks across-then-down.  Same calc inside the editor, but different results based on how I told Tableau to execute each one.

                   

                  The [Diagonal] calc walks the table the same as [index wrap].  If I'm not on an "edge" cell in the table, then do the [Diagonal] calc.


                  And this is what the calc does:

                   

                  The LOOKUP function goes where you tell it to go in the table and grabs whatever you tell it to grab.  The easy part is telling it to grab SUM([Data]).  But which one?

                   

                  The second argument in the LOOKUP is how far back (or forward) from the current position you want to look.  Since I have a negative number there, it will go backward.

                   

                  Inside the parentheses I am calculating how far to go back.  The calc, [number of Dim 1], is an LOD calc that does a COUNTD of [Dim 1].  This tells me how many columns I have in the table.  In this case the value is 4.  If I just used that value as the offset in the LOOKUP, I would get the value directly above the one I am currently on.  But we want to back up one more to get the diagonal, so I do [number of Dim1]+1 and that will go back 5 in this case.

                   

                  I set the [Diagonal] calc to do the same "across-then-down" that [index wrap] does.  So you can see that if I am sitting on [Dim2]="O" and [Dim1]="C", for example, my [index wrap] = 11.  Jump back 5 (using the across-then-down addressing) and you'll see that the SUM(Data) on value=6 (which is [dim2]="N" and [dim1]="B") is 2.

                   

                  The key is telling tableau which way to walk along the grid, and LOOKUP will dutifully go there.

                   

                   

                  The reason I told [Diagonal] not to execute if [index across]=1 is because Tableau will walk back 5 from there.  So if you are on [Dim1]="A" and [Dim2]= "O", for example, it will go from position 9 to position 4 (which is "M"/"D") and that is not a diagonal as you want it to be.

                  • 6. Re: Is it possible to create a calculated field across rows and columns
                    Dave Eckholm

                    Got it - I had it in my head that going backward was rows first but your explanation cleared it up - appreciate all the help

                     

                    Dave