
1. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?
Tracy Rodgers Jul 3, 2012 12:01 PM (in response to ral.duncan)Hi Ralph,
Would you be able to post the workbook (twbx file), as well as giving an example of what the output would look likei.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...?
ral.duncan Jul 3, 2012 12:30 PM (in response to Tracy Rodgers)Thanks for your help. I've attached a workbook and an excel doc. that shows how the matrix should basically look.
Thanks.

Spread_Matrix.xlsx 15.9 KB

Nate_Gas_Dashboard.twb.zip 44.2 KB


3. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?
ral.duncan Jul 5, 2012 7:31 AM (in response to ral.duncan)Anyone....?

4. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?
Jonathan Drummey Jul 5, 2012 9:47 AM (in response to ral.duncan)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...?
ral.duncan Jul 5, 2012 10:03 AM (in response to Jonathan Drummey)Jonathan,
I've attached a .twbx. Thanks for taking a look.
Ralph

Nate_Gas_Dashboard.twbx.zip 69.4 KB


6. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?
Jonathan Drummey Jul 6, 2012 11:54 AM (in response to ral.duncan)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 toplevel 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 redgreen color spectrum, I changed it to orangeblue 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 pixelwide white lines on my screen that I think are artifacts, because even with borders turned off I don't see them.
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

Nate_Gas_Dashboard jtd edit.twbx.zip 457.3 KB


7. Re: Is it possible to take the difference between a row and a column in a matrix like spread...?
ral.duncan Jul 6, 2012 6:20 PM (in response to Jonathan Drummey)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.