3 Replies Latest reply on Oct 27, 2015 6:02 AM by Shawn Wallwork

    How to create a row grand total for a Difference (Quick Calculation)?

    Daniel Carr

      Hello,

       

      We have a numerical field 'UnrealizedPnL' which we used a 'Difference' quick table calculation for.  This works correctly.

      So data, looks like this:

      PortfolioJan 2011Feb 2011Mar 2011Apr 2011Total
      A-123+144-78+455???
      B141353-43252134???
      C1351432-235424355???

       

       

      So, all of the numbers are month over month differences.  These are correct.

      The problem is, when we select 'Show Row Grand Totals' the results are blank.

      Any way to display the SUM(Difference(field))?

       

      Any help greatly appreciated.

       

      Thanks,

      Dan

        • 1. Re: How to create a row grand total for a Difference (Quick Calculation)?
          Joe Mako

          Dan,

           

          There are a few options to get the results you are looking, attached is one option. It does make use of a few interesting concepts of Tableau, using a nested table calc to detect if it is in the grand total, and then performing a different calculation.

          • 2. Re: How to create a row grand total for a Difference (Quick Calculation)?
            Bernd Langer

            Hi Joe

             

            thanks for your post.

             

            Trying to adapt it to a client's request I tried to understand the code of [Difference (with Total)].

            Following the structure of the nested IF..THEN, I have the following remarks resp. questions:
            a) Is the highlighted term not an unnecessary piece of code?. I think one could replace it by just LAST()as the programm cursor already confirmed FIRST()==0

            b) Don't we need an ELSE statement for the inner IF..THEN or does the case of fullfilling [In Grand Total] AND NOT FIRST()==0 never happen ?

             

             

            IF [In Grand Total] THEN

              IF FIRST()==0 THEN

              WINDOW_SUM(

                SUM([UnrealizedPnL]) - LOOKUP(SUM([UnrealizedPnL]), -1),

                0,

                IIF(FIRST()==0,LAST(),0)

                )

              END

            ELSE

              SUM([UnrealizedPnL]) - LOOKUP(SUM([UnrealizedPnL]), -1)

            END

            • 3. Re: How to create a row grand total for a Difference (Quick Calculation)?
              Shawn Wallwork

              Bernd this is a four year old thread, and the code in red was constructed by Richard Leeke as a workaround for a performance issue in the 6.x days. It kept T from cycling through every record. It is now completely unnecessary. I'm fairly sure you can get rid of both the start & end. Try this:

               

              IF [In Grand Total] THEN

                IF FIRST()==0 THEN

                WINDOW_SUM(

                  SUM([UnrealizedPnL]) - LOOKUP(SUM([UnrealizedPnL]), -1)

                  )

                END

              ELSE

                SUM([UnrealizedPnL]) - LOOKUP(SUM([UnrealizedPnL]), -1)

              END

               

              --Shawn