1 2 Previous Next 15 Replies Latest reply on May 7, 2016 11:37 PM by Chandra Bhaskar

    dynamic subtotal 1 to N

    Kevin Andrist

      I'm controlling my data view by a parameter to select the Top 'N' after the data has been ranked and sorted. 

       

      What I'd like to do is generate a dynamic subtotal of the Top 'N' selection.  When I add in the standard Subtotal of a Dimension I get the subtotal of the entire dimension within the data (by definition...), but that's not what I want.

       

      I could try to explain this further, but one example I brought home from last October's users meeting in Vegas is attached. 

       

      Notice (in this case) the Grand Total in each worksheet is not the sum of the N = 5 customers sales, nor is the 100% the sum on the N = 5 % of sales just above. 

       

      How can I get a dynamic subtotal for each measure as a function of N = some number? 

       

      thx..

        • 1. Re: dynamic subtotal 1 to N
          Kevin Andrist

          thoughts..anyone? 

           

          This seems like a fairly useful (and logical) functionality to accompany a top 'n' analysis..maybe it isn't possible?

           

          thanks..

          • 2. Re: dynamic subtotal 1 to N
            Kevin Andrist

            Ok..does anyone know of a loop-function that works in Tableau.  (loop function = subroutine: do some set of instructions 1 to N where N = somenumber)

             

            I remember loop functions from pascal and fortran programming m-a-n-y years ago.  I'm thinking I could use rank/index() & the 1 to N parameter entry as the entry point of "somenumber".

             

            no?

            • 3. Re: dynamic subtotal 1 to N
              Jonathan Drummey

              Here you go, no looping necessary, just a bit of Joe Mako wisdom. See the attached workbook, I've also put my explanation here. I had  a couple of fascinating discoveries along the way:

               

              - The Grand Total row can be made to show most anything you want.

              - The built-in calculations for the Grand Total row seem to reduce the overall level of detail as much as possible to get to single values, but we can expand them by using the Level of Detail shelf and nested table calculations.

               

              I started by creating a % of Total sales for Listed Customer that works by using the following calculation that takes into account the parameter:

               

              SUM([Sales])/WINDOW_SUM(IF INDEX() <= [Top n Parameter] THEN SUM([Sales]) END)

              The Grand Total for this column sums to 100%.

               

              However, I had trouble adjusting the Grand Total for SUM([Sales]) to make that dependent on the parameter.

              I used Joe Mako's calc here: http://community.tableau.com/message/174251#174251 to ID whether the row was the Grand Total row or not. I'd thought I could use a flavor of the calc he made to change how the rows were calculated.

               

              Therefore, I created a "Grand Total Flag" field with the following calculation:

              TOTAL(MIN([Customer Name])) != TOTAL(MAX([Customer Name]))

               

              Setting that to Compute Using Pane (Down) makes it work.

               

              Then I did some other calcs - INDEX(), SIZE(), and LAST() - to explore this partition. It seems that the Grand Total row lives in its own partition that has only one row, because Customer Name is the only dimension in the view. So, all the calculations trying to get at the set of customers that are shown based on the Table Calc filters fail because there's only one row in the partition. Another sign that this is the case is that all the fields in the Grand Total row only show one value, with no overlapping text.

               

              In Joe's post, he had added a copy of the dimension of interest to the Level of Detail. Here, I did that with a copy of Customer Name on the Level of Detail shelf. Now, lots of calculations start breaking, but there's good news - the Grand Total field now shows overlapping text, and the Size() field now shows 795 rows available in the Grand Total partition.

               

              One change needed here to get the Top N filter to work is that it needs to be on the Level of Detail shelf. Filters on table calcs only seem to "see" the pills in the Columns and Rows shelves for Compute using, and instances of themselves on the Level of Detail.

               

              In order to have the Grand Total calcs work differently between the regular rows and the Grand Total row, we set them up as so:

               

              Grand Total Flag

               

              Put the Grand Total Flag on the Level of Detail shelf, and set the Compute using... to Customer Name (copy). This is a convenience so the other table calcs will inherit the value.

               

              Sales and Grand Total:

               

              Here's the calc:

               

              IF [Grand Total Flag] THEN

              IF FIRST()==0 THEN

              WINDOW_SUM(SUM([Sales]),0,[Top n Parameter])

              END

              ELSE

              SUM([Sales])

              END

               

              The IF FIRST()==0 part is to prevent overlapping text on the Grand Total row. Once this pill is on Measure Values, go the Edit Table Calculation... menu option and set the Compute using for Sales and Grand Total to Advanced… with both Customer Name and Customer Name (copy) in the Compute Using, with Order Along set to Sales - Sum - Descending. This gets the partition set up properly to for the sum of sales to only count the sales up to the Top N parameter.

               

              % of Total Sales

              Here's the calc:

              IF [Grand Total Flag] THEN

              IF FIRST()==0 THEN 1 END

              ELSE

              SUM([Sales])/WINDOW_SUM(IF INDEX() <= [Top n Parameter] THEN SUM([Sales]) END)

              END

               

              Again, once this pill is on Measure Values, go to Edit Table Calculation.. and set the Compute using... for % of Total Sales to Advanced… with both Customer Name and Customer Name (copy) in the Compute Using, with Order Along set to Sales - Sum - Descending. This gets the partition set up properly to for the sum of sales to only count the sales up to the Top N parameter.

               

              Let me know if this works for you!

              • 4. Re: dynamic subtotal 1 to N
                Kevin Andrist

                Thanks for responding Jonathan and taking the time to think about this question.  Seemingly simple questions can have not so simple answers.

                 

                I just got the chance about 30 minutes ago to look through your thoughts/approach.   While I haven't digested it yet, I suspect the simple *.twbx file I attached earlier may have been too simple in that it only had the Grand Total & no subtotaling. My guess is this will have a significant impact on the potential solution.

                 

                I've attached a sample workbook that contains a small subset of the real data.  You'll note it does have multiple subtotals as well as the Grand Total.

                 

                I've also attached a graphic (*.xlsx) to indicate the (dynamic) subtotals I'd like to determine.  

                 

                It's completely understandable if you don't have the time to work through this.  I've been trying off and on for several days..it eludes me.

                 

                thx..

                • 5. Re: dynamic subtotal 1 to N
                  Jonathan Drummey

                  Hi Kevin,

                   

                  Either simple questions don't have such simple answers, or maybe the question wasn't so simple in the first place. *grin*

                   

                  I think this can be done, I started at a proof of concept using the superstore sales data and it's possible to specifically identifty subtotal rows along with grand total rows. However, I have two caveats:

                   

                  - The calculations are really sensitive. There's a way that adding a table calculation to a view can sometimes change the results of other table calculations that I don't have a full understanding of yet that I was running into as I worked on the view.

                   

                  - The performance of the view takes a dive because of the quantity and complexity of the table calculations (and eventually the size of the data). You're going to need a custom table calculation for each column that depends on the results of two other table calculations (to identify whether the row is a sub total or grand total) in order to perform a third, fourth, and/or fifth table calculation (for the detail row, sub total, or grand total). And then making this view parameter-driven slows down Tableau even more because it can't really cache any results.

                   

                  This is one of those places where a batch reporting tool might be better suited.

                   

                  The basic process I'd used was to add the Product Name to the Rows shelf and Product Name (copy) to the Level of Detail. This breaks the Top N filter, which needs it's Compute using set to Product Name & Product Name (copy) for it to work. Then I created a Sub Total Flag that looked like the Grand Total, only based on the Product Name instead of Customer Name, and once on the Level of Detail set it's compute along to Product Name (copy).

                   

                  Then the calculations for each value could be something like:

                   

                  IF [Grand Total Flag] THEN

                    (do grand total calc)

                  ELSEIF [Sub Total Flag] THEN

                    (do sub total calc)

                  ELSE

                    (do calc for detail row)

                  END

                   

                  Where I stopped is that with the new Product Name/Product Name (copy) dimensions in the view, the Grand Total Flag starts returning multiple values and I ran out of time to figure that out. You'd need to get that working, and I'm wondering whether the grand total calc and sub total calcs in the above formula might need to be separated out so their Compute using's could be custom set in the view.

                   

                  Hopefully this gives you enough to get a start if you want to continue going this route. It's a fascinating problem to me, I could take another swing at it towards the end of next week (I've got a few dashboards that are demanding attention) if you need help. Or if anyone else here on the forums wants to have a go at this, I'd be happy to see what they can come up with.

                   

                  Jonathan

                  • 6. Re: dynamic subtotal 1 to N
                    Kevin Andrist

                    Jonathan,

                     

                    I believe someone once said, "for every question there is a simple answer" (probably a corruption of Occam's razor).

                     

                    Someone else followed this with, "for every question there is a simple, and incorrect, answer" (sounds like something H.L. Mencken would say).

                     

                    And then there is real life where seemingly simple questions have really involved  answers.  From your description I think we've entered the twilight-zone. While I'm sure it can be done, 'diminishing returns' comes to mind as this was a nice-to-have (some window dressing to the heavy lifting the rest of the workbook is doing) and not a have-to-have. If it risks corrupting other calcs, and that isn't caught, then one has to question whether it's worth the effort.  Risking confidence in the good stuff the workbook does wouldn't be worth it. 

                     

                    With respect to speed..this workbook is an inventory management tool where its current (development) state is about 120,000 records(120,000 rows x 28 columns).  Once it goes into actual use it'll grow to, and level off at around 300,000 records(rows), all stored & preprocessed in Excel, at this point.   Currently the Excel file is 42MB.  So far Tableau performs well with a live connection to the data, but once it grows up I may have to convert it to MS Access for preprocessing and storage, or convert it monthly to a twbx file (I run Tableau desktop). 

                     

                    If the subtotal-sums are really needed we can either eyeball the data and sum it in our heads(easy enough estimate) or take the data into Excel and subtotal it in a minute (as I did with the file attached above)...with no chance of un-noticed errors.

                     

                    Thanks for at least working through and estimate of what's needed to do this.  That in itself is a very valuable answer.

                     

                    Thanks!

                    • 7. Re: dynamic subtotal 1 to N
                      Jonathan Drummey

                      You're welcome! 'Diminishing returns' sounds like an accurate summation to me. And 300K rows for the number of table calcs that are required would probably result in non-acceptable performance, since Tableau would have to be doing calculations across all 300k rows multiple times for the view.

                       

                      Also, way back at the top you'd asked about any looping functions in Tableau, there aren't really any. The closest one that I know of is the PREVIOUS_VALUE() function which returns the previous value of the _calculation_ for the prior row in the partition and the given value for the first row in the partition.

                       

                      Jonathan

                      • 8. Re: dynamic subtotal 1 to N
                        Mac Miller

                        It seems the Grand total calc's are now incorrect on this one.  I downloaded the twbx and on the "Is this what you want?" workbook the dynamic grand total doesn't reflect the top n sum of sales.  Is this something that has possibly occured with the 8.1 upgrade?

                         

                        Has something been left out of context as a reason why this is happening?

                         

                        TY,

                        Mac

                        • 9. Re: dynamic subtotal 1 to N
                          Mac Miller

                          It seems as if the Sales and Grand Total calculation is creating a Grand Total that takes the last of the "n" (let's say the fifth position of Top "n" set to 5), and somehow adds it twice.

                          • 10. Re: dynamic subtotal 1 to N
                            Mac Miller

                            This seems to work:

                             

                            IF [Grand Total Flag] THEN

                             

                            IF FIRST()==0 THEN

                              WINDOW_SUM(SUM([Sales]),0,[Top n Parameter] - 1)

                            END

                             

                            ELSE

                              SUM([Sales])

                            END

                            • 11. Re: Re: dynamic subtotal 1 to N
                              Mac Miller

                              Jonathan Drummey

                               

                              Oh heck, after reading ALL the text in your solution, i realize that you had stated that the calcs were adding multiple values and you just ran out of time....

                               

                              Well, i think i came to a fix here.

                              • 12. Re: dynamic subtotal 1 to N
                                Mac Miller

                                Now that i think of it... since this post is 2 years old, has a more streamlined approach been included in Tableau?  If so, i couldn't find it, hence my search for Dynamic Grand Totals Top N calcs.

                                • 13. Re: Re: dynamic subtotal 1 to N
                                  Jonathan Drummey

                                  Hi Mac,

                                   

                                  The only new features in the product supporting Top N and grand totals are the RANK functions and the multi-pass totals introduced in 8.1. However, neither solves the issue here. I've found solutions for this problem, but haven't written anything up about it because the exact solutions are so particular to the data & desired view that I haven't been able to come up with something generalizable.

                                   

                                  Jonathan

                                  • 14. Re: dynamic subtotal 1 to N
                                    ratnakishore pv

                                    Hi Kevin ,

                                     

                                    if i understood correctly ,you are looking for top N and rest should be sum-ed up with other .

                                     

                                    If Yes then below is solution

                                     

                                    Regards,

                                    Kishore

                                    1 2 Previous Next