
1. Re: Using Table Calculation, is there a funtion which provides me a value of the Cell it is rendering( this operator in OOL)
Dana Withers Aug 23, 2013 1:35 AM (in response to nitin sareen)I'm not following this... Can you explain further what your data looks like?
I'm already confused how sum of sales can add up to two different things and what you mean with what should be different measures.
Can you give an example of your data structure and values?

2. Re: Using Table Calculation, is there a funtion which provides me a value of the Cell it is rendering( this operator in OOL)
Wilson Po Aug 25, 2013 6:20 PM (in response to nitin sareen)Hi Nitin,
I'm also having a little trouble understanding the structure of data you are running into. But with that said, I think what you are trying to refer to is the LOOKUP function for Table Calculations. The functionality has the ability to reference any result set returned anywhere in the view (and thus is important part for doing comparisons). That being said, with the view we have above, we should not need to use any custom Table Calculation, as there is already a Percent Difference quick table calculation available as a template. See if the link below helps out:
Calculating Percent Difference Across Dimensions  Tableau Software

3. Re: Re: Using Table Calculation, is there a funtion which provides me a value of the Cell it is rendering( this operator in OOL)
nitin sareen Aug 25, 2013 9:10 PM (in response to Dana Withers)Hi,
I assumed there would be confusion, I'm actually caught up in a lot of things, so was not able to write the entire problem statement, Hopefully you would understand what I'm trying to do in this summary.
Summary:
Objective : Display a GRID, which contains the Measures ( Sales, Investment) for two different Plans( Dimension with values A & B). The measures should be visible for each dimension value( Plan A's Sales , Investment & Plan B's Sales & Investment).
So essentially this is the GRID which is required.
Sales Plan A Plan B
(% Difference from Plan A)
Plan A Plan B
(% Difference from Plan A)100 150
50%
100 50
50%
100 150
50%
100 50
50%
Here the Column under Investment & Sales which is Plan B contains two values 1) Calculated Measure SUM(Investment) or Sum( Sales ).
2) % Difference from Plan A ( for Sales & Investment).
I have attached the Image which indicates what i'm trying to do.
My problem is that i have to represent two values in the same cell i.e Plan B needs to show the Sum(Investment) for Plan B and the % Difference of Investment from Plan A to Plan B in the same cell.
My Challenge is that i cannot create a formula which tells Tableau to render %Diff of Investment in the Cell under Investment vs % Diff of Sales in the cell under Sales, since Investment & Sales are Measure Names which are not available in Table Calculations which i can use in my IF condition. e.g. IF Measure Name = 'Investment' THEN " Do this" ELSE " Do That". If i achieve this my problem is solved.

Sales_Sample_01.jpg 307.8 KB

Sales_Sample.jpg 263.4 KB


4. Re: Re: Re: Using Table Calculation, is there a funtion which provides me a value of the Cell it is rendering( this operator in OOL)
Dana Withers Aug 26, 2013 8:53 AM (in response to nitin sareen)1 of 1 people found this helpfulHello,
I don't think what you need here is possible. If it is possible to reshape your data though it would be very easy:
But to make the above, I reshaped the data so that your "new" and "old" plan a and b are one dimension. That way all values are in the same measure and can be calculated as a percentage of difference per dimension.
Hope that helps,
Dana 
5. Re: Re: Re: Using Table Calculation, is there a funtion which provides me a value of the Cell it is rendering( this operator in OOL)
Jim Wahl Aug 26, 2013 9:46 AM (in response to nitin sareen)Hi Nitin,
Another approach and my first thought when you said grid and, "IF Measure Name = 'Investment' THEN ..." was to use a scaffold. A scaffold allows you to refer to measure names in calculated fields.
The basic idea behind a scaffold is to build a data grid and use blending to add data to it by using IF/THEN statements for each grid intersection.
You could accomplish the same thing by modifying the data with ETL or custom SQL, but scaffolding avoids some of the pitfalls of those approachesmainly, that you're often creating extra data that can cause problems in other calcs that, for example, use Number of Records, ....
I used Tableau's Coffee Store set to mock this example up. In your grid above Plan A and Plan B are Departments Furniture and Technology, respectively. Your Sales and Investment are Profit and Sales. I used Region for the rows. The result looks like this:
To get this, I built the scaffold which includes a row for each combination of measure names and dimensions. In this case Profit/Sales and Region, Department, and Row. Row is hidden above, but each Region has a Row 1 and Row 2. The Scaffold looks like this:
I used Tableau's Excel reshape plugin to create this, but if the Region or Department dimensions will change, you could do this in SQL.
Next I added this data source in Tableau. Selected it and added the dimensions to the view:
Now that you have a scaffold, you can add data using blending: I created a field in the scaffold data source called Value:
IF MIN([Measure]) = 'Profit' AND MIN([Row]) == 'Row 1' THEN SUM([Sample  Superstore Subset (Excel)].[Profit]) ELSEIF MIN([Measure]) = 'Profit' AND MIN([Row]) == 'Row 2' AND MIN([Department]) == 'Technology' THEN (SUM([Sample  Superstore Subset (Excel)].[Profit])  LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Profit]), 1)) / LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Profit]), 1) ELSEIF MIN([Measure]) = 'Sales' AND MIN([Row]) == 'Row 1' THEN SUM([Sample  Superstore Subset (Excel)].[Sales]) ELSEIF MIN([Measure]) = 'Sales' AND MIN([Row]) == 'Row 2' AND MIN([Department]) == 'Technology' THEN (SUM([Sample  Superstore Subset (Excel)].[Sales])  LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Sales]), 1)) / LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Sales]), 1) END
The formula should be pretty clear. Note that you have to use aggregates MIN(Measure), because Tableau can only pull aggregated data from the blended data source. In this case MIN(Measure) == MAX(Measure) == ATTR(Measure), but MIN() or MAX() will be a little more efficient than ATTR(), which evals to IF MIN() == MAX(). ...
Now you can drag the Value to the view. And the scaffold will be filled.
One problem is that you have two different number formats: a regular number with no decimal and a "%". To handle this, I converted the numbers to strings. Value 2 looks like:
IF MIN([Measure]) = 'Profit' AND MIN([Row]) == 'Row 1' THEN "$" + STR(ROUND(SUM([Sample  Superstore Subset (Excel)].[Profit]))) ELSEIF MIN([Measure]) = 'Profit' AND MIN([Row]) == 'Row 2' AND MIN([Department]) == 'Technology' THEN STR(ROUND((SUM([Sample  Superstore Subset (Excel)].[Profit])  LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Profit]), 1)) / LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Profit]), 1) *100)) + "%" ELSEIF MIN([Measure]) = 'Sales' AND MIN([Row]) == 'Row 1' THEN "$" + STR(ROUND(SUM([Sample  Superstore Subset (Excel)].[Sales]))) ELSEIF MIN([Measure]) = 'Sales' AND MIN([Row]) == 'Row 2' AND MIN([Department]) == 'Technology' THEN STR(ROUND((SUM([Sample  Superstore Subset (Excel)].[Sales])  LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Sales]), 1)) / LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Sales]), 1) * 100)) + "%" END
Finally, with this approach, you could ignore the row ID and simply add a hard carriage return CHAR(10) and the "% diff" when in the Technology column.
IF MIN([Measure]) = 'Profit' THEN "$" + STR(ROUND(SUM([Sample  Superstore Subset (Excel)].[Profit]))) + IF MIN([Department]) == 'Technology' THEN CHAR(10) + STR(ROUND((SUM([Sample  Superstore Subset (Excel)].[Profit])  LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Profit]), 1)) / LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Profit]), 1) *100)) + "%" ELSE "" END ELSEIF MIN([Measure]) = 'Sales' THEN "$" + STR(ROUND(SUM([Sample  Superstore Subset (Excel)].[Sales]))) + IF MIN([Department]) == 'Technology' THEN CHAR(10) + STR(ROUND((SUM([Sample  Superstore Subset (Excel)].[Sales])  LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Sales]), 1)) / LOOKUP(SUM([Sample  Superstore Subset (Excel)].[Sales]), 1) * 100)) + "%" ELSE "" END END
If you choose the first option with the row dimension, you can hide this column by clicking on the Row pill and unselecting Show Header.
Jim

ScaffoldExample.twbx.zip 1.5 MB


6. Re: Re: Re: Using Table Calculation, is there a funtion which provides me a value of the Cell it is rendering( this operator in OOL)
nitin sareen Aug 26, 2013 9:32 PM (in response to Jim Wahl)Thanks Jim!!!
This looks like something will help me out, to achieve what I'm trying to do. though it's lot of information for me to digest being a newbie to Tableau. Will try to understand and see how you have come up with this.
Will post back if I'm successful adopting the Scaffold approach you have mentioned here.
Out of curiosity, why does Tableau not provide a capability to do this OOTB ??
Thanks Again!!
Nitin

7. Re: Re: Re: Using Table Calculation, is there a funtion which provides me a value of the Cell it is rendering( this operator in OOL)
nitin sareen Aug 26, 2013 9:33 PM (in response to Dana Withers)Thanks Dana,
Outcome looks good, could you please provide me the .tbx file i can analyze to see how you came up with this.
Thanks!!
Nitin

8. Re: Re: Re: Re: Using Table Calculation, is there a funtion which provides me a value of the Cell it is rendering( this operator in OOL)
Dana Withers Aug 27, 2013 1:14 AM (in response to nitin sareen)Hello,
Workbook attached
Hope this helps,
Dana

SalesInv.twbx.zip 9.4 KB
