2 Replies Latest reply on Aug 17, 2012 7:29 AM by Jonathan Drummey

    Grand Total Calculated With Window_MAX()?


      Just a general question.  Not sure this is a bug or just a result of window functions.


      I have a row of data that's a calculated field which uses a window_max() value.  The values I get are correct, but when I do a row total, the Grand Total shows me the value without the effect of the window_max() formula I used in the calculated field.



      My DDS is the value without getting calculated against the window_max() formula.  The Optimized Lead Sales is the value with the window_max() formula.  My Grand total for DDS should be 1243, but the Optimized Lead Sales Total should be 1290.  Is this a glitch? Or just something to do with window functions?

        • 1. Re: Grand Total Calculated With Window_MAX()?
          Peter Hopwood



          I can see the problem - I think this occurs because table calcs like window_max are the last thing to be calculated - so the Grand Total is calculated first-  hence it provides a 'grand total' of the original values (DDS) not the grand total of the window_max() function.


          How about creating a new table calc that does a Sum of the window_max() calculation - so that it will be calculated after the window_max() for the cells - to provide your Grand Total?


          Hope this helps.



          • 2. Re: Grand Total Calculated With Window_MAX()?
            Jonathan Drummey

            @Peter: This is not an issue with order of calculation, it's a question of the overall level of detail in the view. Grand Total columns and rows are a separate calculation at a different level of detail. For column Grand Totals, whatever discrete pills are on the Rows shelf are ignored, for row Grand Totals, whatever discrete pills are on the Columns shelf are ignored.


            For table calculations appearing in the Grand Total row/column, what typically happens is that they show odd results because calculation is operating at a higher (more coarse) level of aggregation since the Grand Total computation has effectively removed pills from the view.


            @Abigail: Without seeing what pills are in your view and the calcs, I can't be completely sure, but I believe what's happening is that the original calculation is being evaluated across your dataset and the WINDOW_MAX is having no effect because necessary dimensions (for the WINDOW_MAX) are not available in the Grand Total.


            It seems like what you want is a sum of the results of the calc to appear in the Grand Total row. This would require changing how Tableau computes the Grand Total, I just did a blog post on how to do this at http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/.





            1 of 1 people found this helpful