6 Replies Latest reply on Feb 26, 2016 5:22 AM by Jonathan Drummey

    Grand total of absolute running totals

    jack youldon

      Hi,

       

      My problem data set looks like this:

       

      quantityassetdate
      10a1/01/2015
      10a1/02/2015
      -10a1/03/2015
      -10b1/04/2015
      -10b1/05/2015
      10b1/06/2015

       

      I am trying to keep an absolute running total over time of net position in each asset.   So for example a position of -10 in asset A and a position of 10 in asset B would sum to a total position of 20.   I am bumping up against the (seemingly common) problems that I can't

       

      1) Combine LOD calculations with table calculations

      2) Compute a grand total of the numbers actually on the sheet instead of the underlying values (as you can see from the below attempt the ABS() is not being taken account of by the Total.

       

       

      Very (very) grateful for any help, please let me know if you see a problem or if the question is unclear.

       

      Jack

        • 1. Re: Grand total of absolute running totals
          Maciek La

          have You tried doing calculated field with abs(quantity) and running_total on this field? insted of abs(running_total)

          • 2. Re: Grand total of absolute running totals
            jack youldon

            that would not work unfortunately as I still want the quantity to net on a per asset basis.  I am just calculating the long or short position of each asset as trades are done and the total balance sheet used.

            • 3. Re: Grand total of absolute running totals
              jack youldon

              I was able to get fairly close to what I wanted by creating a secondary table calculation, which sums from top to bottom.  This total used the formula

               

              if LAST()==0 then RUNNING_SUM([abs  running sum]) END

               

               

              The solution is still not perfect as the formatting is pretty poor.  I'm also not sure if i will be able to produce a graph, which just shows the overall total per month without lots of junk from the asset level detail.

               

              Any improvements still welcomed and thanks to Jonathon Drummey for his helpful series on the topic.

               

              Customizing Grand Totals – Part 1 | Drawing with Numbers

              • 4. Re: Grand total of absolute running totals
                Jonathan Drummey

                Hi Jack,

                 

                It seems like there are two issues here:

                 

                1) get the total to have the correct grand total, as in this:

                 

                 

                 

                2) create a graph of the total, as in this view:

                 

                 

                The reason why these are separate is because a) *grand totals are a separate computation* in Tableau and b) grand totals are what I all a "dead end" calculation - we can't do anything with their results, such as a use that grand total to make a line chart while hiding the "...junk from the asset level detail."

                 

                I set up both of the above views in the attached v9.2 workbook.

                 

                1) The crosstab workout uses the duplicated dimension technique from http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/ to add Asset (copy) to the level of detail Shelf with a nested table calculation. The RS Quantity is the Tableau default running sum that addresses on Date. The Final Quantity has the formula IF FIRST()==0 THEN WINDOW_SUM(ABS([RS Quantity])) END and a nested addressing on Asset (copy) so it partitions on each Date. So the inner Running Sum computes one way and the outer calc gets desired result.  For the crosstab view worksheet I duplicated the worksheet, moved pills around, Ctrl+dragged the Final Quantity pill to the Filters Shelf and set it to filter for non-Null values and set Analysis->Stack Marks->Off.

                 

                That gets an accurate Grand Total in a crosstab, the line chart is easier because we don't have to do the extra work to deal with the grand total being a separate computation at its own level of detail. Here's the line chart instructions.

                 

                2) The line chart workout uses the same calculated fields however we don't need the duplicated dimension and the Final Quantity field has a nested addressing on Asset. I duplicated this worksheet, moved pills around, Ctrl+dragged the Final Quantity pill to the Filters Shelf and set it to filter for non-Null values, and finally turned off the tooltip for the Asset pill. Note that the Asset pill is on the Level of Detail Shelf, this is necessary to ensure that the running sum is accurately computed for each Asset before those results are summed by the Final Quantity calc.

                 

                LOD expressions are not suitable for this kind of view for two reasons: a) LOD expressions don't currently support running sums, and b) LOD expressions are computed *before* table calculations, so if we use the running sum table calculation we can't use those results inside an LOD expression. The workaround there is to nest table calculations.

                 

                If you have any questions let me know!

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Grand total of absolute running totals
                  jack youldon

                  Yes!  Thank you, the key thing I had missed was the meaning of having the Asset on the marks shelf, without anything next to it.

                   

                  I was actually able to clean up my table pretty nicely by hiding rows, (as per Tinkering with Filtering vs. Hiding « Tableau Tinkering) without having to duplicate the dimension... will see if the current format is sufficient but good to have the alternative in reserve.

                   

                  thanks again

                   

                  Jack

                  • 6. Re: Grand total of absolute running totals
                    Jonathan Drummey

                    I'm glad you got something that worked for you!

                     

                    FYI a complication of  hiding rows is that if the data changes and new

                    values are added (such as a C asset) then you'll have to manually hide

                    them. Using the measure on the Filters Shelf automatically removes them.

                     

                    Jonathan

                     

                     

                    On Fri, Feb 26, 2016 at 2:22 AM, jack youldon <tableaucommunity@tableau.com>