3 Replies Latest reply on Feb 11, 2019 10:16 AM by Barb Reiser

    Calculate Difference when using % of Total

    Barb Reiser

      Does anyone know how I would write a calculation to tell me what the difference would be in this view between 2018 and 2019 for columns C and G? For example, in column C, looking at Plan B, the difference would be 1.69% (5.26-3.57).


        • 2. Re: Calculate Difference when using % of Total
          Joe Oppelt

          What I did:


          I copied your sheet to preserve what you had.


          On my copy, I made my own calc for % total.  The reason is that I need one table calc to walk the table DOWN, but the DIFF calc to work across the years for each group within each Plan.


          To get my own % total calc, I just dragged your existing quick table calc into my calc editor.  Tableau fleshes out the syntax that it used to create the quick table calc into my calc for me.  (I could have typed out the same thing on my own, but dragging is a whole lot easier!)


          I set the table calc addressing to Table(down) as your original one was, and I get the same values.  That's step 1.

          Next I created my DIFF calc.  Take a look at that.  The LOOKUP function tells tableau to look x-many marks from the current mark.  In this case I told it to look negative-1.  I put that on TEXT, and I had to set the addressing correctly.  Right click on the pill and select EDIT TABLE CALC.  You'll see that I picked specific dimensions -- in a particular order.  Plan is at the top, and then Group.  When I tell it to restart every Group, that implies we are also restarting every dimension above it.  So for each change in Group or Plan, start from the beginning.


          For kicks, change the setting to Table(across).  It will get the first Group right, but for the next group within  the same plan, it will use the 2019 value from the previous group and do the math on those two values.  I want a null in the first year for any group, thus the RESTART.


          Note also, in the EDIT TABLE CALC for my DIFF calc, there is a pull-down where all nested table calcs need to get set:




          So not only do I have to set my DIFF calc, but I also have to make sure that for the DIFF calc, the %Total calc operates in the correct direction.  (And the two table calc do not have to have the same settings here!  That's the key to getting what you wanted.)

          • 3. Re: Calculate Difference when using % of Total
            Barb Reiser

            Thank you! That works perfect!