have You tried doing calculated field with abs(quantity) and running_total on this field? insted of abs(running_total)
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.
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.
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!
grand total for running sum.twbx 26.6 KB
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.
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.
On Fri, Feb 26, 2016 at 2:22 AM, jack youldon <email@example.com>