8 Replies Latest reply on Aug 27, 2013 1:14 AM by Dana Withers

    Using Table Calculation, is there a funtion which provides me a value of the Cell it is rendering( this operator in OOL)

    nitin sareen

      Within a table partition, i want to refer to the cell the function is rendering. Something like the "this" operator in the object oriented languages. I'm trying to overcome a challenge.

       

      I want to make a view which would be something like this.

       

      SalesInvestment

      Plan A

      Sum(Sales)

      Plan B

      Sum(Sales)

      % Difference of Plan A to B

      Sum(Sales[B]) - Sum(Sales[A]) / Sum(Sales[A]) * 100

      Plan A

      Sum(Investment)

      Plan B

      Sum(Investment)

      % Difference of Plan A to B

      Sum(Invest[B]) - Sum(Invest[A]) / Sum(Invest[A]) * 100

      10015050% ( Calculated Measure based on previous two columns)10050-50% (Calculated Measure based on previous two columns)

       

       

      The challenge to make this type of the Grid View is that the (% Diff Column) for Sales & Investment which ideally should be different measures. The value in these columns should be calculated based on what is present in the previous two cells ( Plan A & Plan B). I'm unable to achieve this using table calculations because there is no function in Table Calculations which gives me the column reference in relation to the cell which is getting rendered. I hope i have put my point across what i'm facing here, if anyone has done something like this before, please do let me know.

        • 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

          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

            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

              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 APlan 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.

              • 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

                Hello,

                 

                I don't think what you need here is possible. If it is possible to reshape your data though it would be very easy:

                SalesInvestment.PNG.png

                 

                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

                1 of 1 people found this helpful
                • 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

                  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 approaches---mainly, 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:

                  2013-08-26 18-40-14.png

                   

                  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:

                  2013-08-26 18-46-33.png

                   

                  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:

                  2013-08-26 19-27-06.png

                   

                  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

                  1 of 1 people found this helpful
                  • 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

                    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

                      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