8 Replies Latest reply on Sep 30, 2016 12:26 PM by Dhanashree Arole

    Add, Subtract from separate tables

    Galen Flynn

      Hello,

       

      I was wondering if someone could help me add or subtract from separate tables. I apologize this data isn't exactly my company's (for obvious reason) and the situation is hard to recreate.

       

      But basically, I have two tables and I need to somehow lookup the value from table 2 and subtract from table 1.

       

           TABLE 1                         TABLE 2

      Date     Units                    Date     Units

      Date1     123                    Date1     100

      Date2     123                    Date2     50

      Date3     123                    Date3     25

       

      GRAND TOTAL

      Date     Units

      Date1     23

      Date2     73

      Date3     98

       

      The data was hard to stage, but basically my filter does not apply to all the data so I can't simply filter out on the grand total.

      Any help is appreciated. Thank you!

       

       

      Please see attached workbook

        • 1. Re: Add, Subtract from separate tables
          Joe Oppelt

          Can't open twb files.  You have to post a twbX (Extract Packaged Workbook).

           

          But you need to blend the two data sources on the date fields, and you use data from the secondary source as an aggregate.  Always as an aggregate.


          So create a calc in the primary source that looks something like this:

           

           

          SUM([UNITS]) - SUM([secondary source name].[UNITS])

           

          In fact, when you drag the [UNITS] from the secondary source into the primary-source calc when you are editing, Tableau will automatically insert "SUM([secondary source name]." for you.

          • 2. Re: Add, Subtract from separate tables
            Galen Flynn

            Hi Joe,

             

            Thank you again for the support. The only problem is my data is coming from one source.

             

            When I say tables, I mean I created a view for two separate sums. (one sum is filtered).

            So logically it would be something like,

             

            Grand Total          =

            Total [Filter1]        +

            Total [Filter2]

             

            And what I need is

             

            Excluded Total      =

            Grand Total           -

            Total [Filter1]         -

            Total [Filter2]

             

             

            I don't think blending is an option. I currently have these in Excel as formula:

             

            ExcludedTotal!A1 = Sheet1!A1 - Sheet2!A1 - Sheet3!A1

             

            I believe the attachment is a .twbx

            • 3. Re: Add, Subtract from separate tables
              Dhanashree Arole

              Hi,

               

              Your attached workbook does not have two tables as mentioned in your post, so I am not sure what exactly you are trying to do in the workbook.

               

              Here is how the dimensions and table look:

               

               

              Can you elaborate what you would like to subtract etc?

               

              More importantly, if you have 2 tables connected in tableau, I would suggest joining them, in your case, based on the Date and then created calculated field that simply performs the subtraction.

               

              Also you can use QUICK TABLE CALCULATION to compute difference across / down / various combinations:

               

               

              Hope that sheds some light!

               

              Thanks,

              Dhanashree

               

               

              • 4. Re: Add, Subtract from separate tables
                Joe Oppelt

                Galen Flynn wrote:

                 

                ...

                 

                I believe the attachment is a .twbx

                Huh,  On my screen that "X" is on a separate line.  I completely missed it.  I've been doing this forum for over 2 years and that has never happened to me before!  My bad.

                 

                So I have it open.  Are you saying you want to subtract the "B" value from the "A" value?  I have created [Calculation1] that does a LOOKUP() function to do that.  See attached.

                • 5. Re: Add, Subtract from separate tables
                  Galen Flynn

                  Joe,

                   

                  Thank you for responding.

                  What I'm trying to do in this scenario is for each group take

                  Type A(Sheet 1) - Type C(Sheet 2) = Grand Total

                   

                  In my data, a filter does not apply to everything so I can't simply filter out type C. So I basically need to subtract Sheet 2 from Sheet 1.

                  • 6. Re: Add, Subtract from separate tables
                    Galen Flynn

                    Dhanashree,

                     

                    Thank you for your response!

                    I'm not sure that a table calculation will work as I am wanting to subtract values on sheet 2 from values on sheet 1.

                     

                    So;

                     

                    Type A - Type C = Grand Total

                     

                    I can't just filter out Type C because in my data, my filter does not apply to everything.

                    • 7. Re: Add, Subtract from separate tables
                      Joe Oppelt

                      You can't subtract sheets from each other.  You have to have it all in one sheet.  (Not necessarily displayed, but within the table.)

                       

                      I can force values to be there.  (See Sheet 4 in the attached.)  But your example is a very simple one and I suspect you don't have just 4 moving parts (A, B, C, D) in your real data.  The way your data is set up in your example doesn't lend itself to what I think you are really looking to do.  Because the way the data is set up, you need one LOD for each [TYPE] value.  (And another for each pair you want to combine.)

                       

                      The dimensionality of your example data tells tableau to consider all the Cs and Ds that reside under A or under B.  I think you want to re-shape the data so that there is only one [TYPE] column, not [Type] AND [Type1].  then you can treat "A" and "C" as equals within the dimension.  The question I still have is how you would know that A and C are to be treated together if you actually have a dozen [TYPE]s instead of 4.  And what if the values for [TYPE] were departments instead of alphabetical values.  How do you math up Department-6 with Department-12...

                      • 8. Re: Add, Subtract from separate tables
                        Dhanashree Arole

                        Galen,

                         

                        Have you considered reorganizing the data? Normalization techniques might be handy.

                         

                        Dhanashree