2 Replies Latest reply on Apr 26, 2017 6:21 AM by Chris C

    Window Difference within a Dimension

    Chris C

      In the sample table below, how can I create a calculate field that subtracts one attribute from another and leave the rest of the attributes the same?  For example, I have selected two pairs to find the difference: Subtract Fasteners from Binders (1,523 - 217 = 1,306) and subtract Tables from Chairs (617 - 319 = 298).  The values for Fasteners and Tables will remain the same. 

       

      I figured this involves using WINDWO_SUM, but the number of attributes display can change, depending on which groups are selected.  In the example below between Binders and Fasteners, there is Bookcases, Chairs, Copiers, and Envelopes.  Depending on my filter, it could take out Chairs and Copiers, which would affect the number of rows for the WINDOW_SUM to partition.

       

      I am using the Superstore dataset in Tableau 10.1.

       

        • 1. Re: Window Difference within a Dimension
          Patrick A Van Der Hyde

          Hello Chris,

           

          I would likely do this with Calculated fields if these groupings are going to be permanent in your data.

           

          For instance - to change the specific values to negative, use:

           

          If [Sub-Category]="Fasteners" then -[Number of Records] elseif

             [Sub-Category]="Tables" then -[Number of Records] else

             [Number of Records]

          end

           

          and then change the Sub-Category field to allow for the desired grouping:

           

          Case [Sub-Category]

          When "Fasteners" then "Binders"

          When "Tables" then "Chairs"

          Else [Sub-Category]

          END

           

          See the right column here:

           

           

           

          Making the changes with the calculated fields will allow you to utilize totals, sub-totals, and other calculations and views with fewer complications.

           

          I hope this helps,

          Patrick

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Window Difference within a Dimension
            Chris C

            Hi Patrick,

             

            Thank you for your solution, however I still need to display "Fasteners" and "Tables".  So we are halfway there.  I am starting to think this may be one of those attempts to perform an Excel-like function within Tableau, meaning applying a calculation to specific rows/columns.