7 Replies Latest reply on Jul 6, 2012 6:20 PM by ral.duncan

    Is it possible to take the difference between a row and a column in a matrix like spread...?

      How, if it is even possible, to take the difference between a row and a column to develop a matrix?  I am trying to take the difference in natural gas prices between different price hubs so I can easily look over the spread between different price hubs.  The measure that I would like to use is the price from the location, called GdToday, which would then be subtracted from another location that has its own GdToday.  In the equation I would like to thave all the row prices subtracted from the column prices.  I have posted a screen shot below of the setup of my worksheet.  If anyone would like me to attach a workbook please let me know and I will post one. Thanks so much, Ralph. 

       

      Gas Matrix.png

        • 1. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?
          Tracy Rodgers

          Hi Ralph,

           

          Would you be able to post the workbook (twbx file), as well as giving an example of what the output would look like--i.e. the desired/actual values?

           

          -Tracy

          • 2. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?

            Thanks for your help.  I've attached a workbook and an excel doc. that shows how the matrix should basically look. 

             

            Thanks. 

            • 4. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?
              Jonathan Drummey

              Hi Ral,

               

              You attached a .twb (Tableau Workbook) file, which does not have the data included. Please export your workbook to a .twbx (Tableau Packaged Workbook), thanks!

               

              Cheers,

               

              Jonathan

              • 5. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?

                Jonathan,

                 

                I've attached a .twbx.  Thanks for taking a look. 

                 

                Ralph

                • 6. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?
                  Jonathan Drummey

                  Hi Ralph,

                   

                  There are a couple of solutions I can think of, it being a Friday afternoon and me being mercifully and temporarily between deadlines I put together both in the attached workbook. The first solution is to use a SQL cartesian join (cross product) to pad the data, so we can generate 4489 marks (67 * 67) instead of the original 67. The second solution is to use Tableau's INDEX() table calculation to pad the data - thanks to Tracy for her work on this post, http://community.tableau.com/message/178976#178976, I had the germ of the idea for the that one.

                   

                  Here's the Custom SQL for the first solution:

                   

                  SELECT [Sheet1$].[BasisToday] AS [BasisToday],

                    [Sheet1$].[BasisYesterday] AS [BasisYesterday],

                    [Sheet1$].[GdToday] AS [GdToday],

                    [Sheet1$].[GdYesterday] AS [GdYesterday],

                    [Sheet1$].[IFerc] AS [IFerc],

                    [Sheet1$].[item] AS [item],

                    [Sheet1$].[latitude] AS [latitude],

                    [Sheet1$].[longitude] AS [longitude],

                    [Sheet1$].[MarkDate] AS [MarkDate],

                    [Sheet1$].[name] AS [Name Across],

                    [Sheet1$].[region] AS [region],

                    [2].[name] AS [Name Down]

                  FROM [Sheet1$], [Sheet1$] AS [2]

                  WHERE [Sheet1$].[MarkDate] = [2].[MarkDate]

                   

                  Note that the original "name" field is now Name Across, and for each Name Across we have all Name Down's.

                   

                  Start with a worksheet with Name Across on Columns and Name Down on Rows. You can see this in the "cartesian crosstab" worksheet.

                   

                  Then I created the following calculated fields:

                   

                  - GdToday Across with the formula ATTR([GdToday]). This is just to clarify the results.

                  - Index Down with the formula INDEX(). This is set to Compute Using Name Down.

                  - GdToday Down with the formula PREVIOUS_VALUE(LOOKUP([GdToday Across],[Index Down]-1)). This is a nested table calculation, with the Compute Using for Index Down set as noted (Name Down), and the Compute Using for the top-level set to Name Across. The LOOKUP part is that for the first column of Name Across it looks up the value of GdToday that would be appropriate for the particular Name Down, and then the PREVIOUS_VALUE part makes sure that value is used for every instance of Name Across.

                  - Spread with the formula [GdToday Across]-GdToday Down. This calc would use the Compute Using settings described above. (In my case, I built these calcs in the cartesian crosstab worksheet and Tableau inherits the table calc settings as you add new calcs, so it's less effort than it takes to describe).

                   

                  From the cartesian crosstab, I duplicated the worksheet and removed all measures except for Spread, and voila! There's your output. For fun I turned this into a heatmap, as you can see in the cartesian heatmap view, using instructions from http://public.tableausoftware.com/views/conditionalformattingv4/Introduction. Though Tableau defaults to a red-green color spectrum, I changed it to orange-blue for it to work for color blind folks. I also created the cartesian all view as well, just to see all the data at once. I see some 1 pixel-wide white lines on my screen that I think are artifacts, because even with borders turned off I don't see them.

                   

                  cartesian all.png

                   

                  So that's using Custom SQL, which for larger data sets can end up pushing more data over the wire and slowing down the results. The second solution uses the original data set, and the INDEX() and LOOKUP() functions to pad the data out, so all those calcs happen inside Tableau. There's one new calculation and one modified calculation for this:

                   

                  Index Across with the formula INDEX(). This has its Compute Using set to Name Across.

                  GdToday Across has a different formula, PREVIOUS_VALUE(LOOKUP(ATTR([GdToday]),[Index Across]-1)). This is necessary because there is only one value of GdToday per combination of Name Across/Name Down, and we need to get to a value for every combination. This is a nested table calc where Index Across uses the same Name Across Compute Using, and GdToday Across uses a Name Down Compute Using.

                  Though the GdToday Down and Spread formulas don't change, their nested Compute Usings get more complex because of the additional table calcs.

                   

                  You can see the intermediate steps in the table calc crosstab view, the resul in the table calc spread view, and a heatmap in the table calc heatmap view. Here I also had to create a padded value for the Size shelf to get that to work, using the formula PREVIOUS_VALUE(ATTR(1.0)) and the Compute Using set to Table Across then Down so it would populate all rows.

                   

                  Finally, if you want just a triangle chart, you can use an alternative version of the Spread calc, I created:

                   

                  Spread Triangle with the formula IF [Index Down] <= [Index Across] THEN [GdToday Across]-[GdToday Down] END

                   

                  This only returns results for the upper/right triangle, you can see that in the table calc triangle view.

                   

                  Cheers,

                   

                  Jonathan

                  • 7. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?

                    Hey Jonathan,

                     

                    Thank you so much for putting this together for me. That was very generous of you.  I am not at my desktop that has Tableau, so I can't go through your work, but just from taking a look at what you put together it looks like you have come up with a solution.  Again, thanks so much. 

                     

                    Ralph.