1 2 Previous Next 16 Replies Latest reply on Sep 18, 2015 2:00 PM by Steve K

    Top N Bottom N Totals Only

    lynda.kroeger

      I am trying to create a recap crosstab showing summary measures for the Top 80% and Bottom 20% products based on a  percentage to total(sales).  In my cross tab at the product level I have a flag showing Top 80% and Bottom 20% and I get my desired results.  Now in the recap I do not want to the list of products.  Essentially I want two rows of data, Top 80% and Bottom 20% totals along with the totals for each measure.  The top/bottom is based on a running sum percentage to total sorted by rank.  Is there any way that I will be able to create this recap?I'm not sur eif this is too complex or so obvious I have missed it completely.

       

      I attached a sample workbook where I have created a similar sitution. The one worksheet shows Items (marked by color) either Top 80 or Bottom 20 % ot total Sales.  The recap worksheet ideally would summarize this detailed info.  I can't get it to break out by top 80%/Bottom 20%.  Thanks for your help.

        • 1. Re: Top N Bottom N Totals Only
          Tracy Rodgers

          Hi Lynda,

           

          Would you be able to post a workbook or screenshot so that we can get a better idea of what you want to accomplish?

           

          -Tracy

          • 2. Re: Top N Bottom N Totals Only
            Jonathan Drummey

            Hi Lynda,

             

            Thanks for posting a packaged workbook. I set something up in the attached.

             

            There are two issues in getting to your final view. The first was that you didn't have Item in the view when the calculations depend on the item aggregation - the goal is to separate the 80/20 by the % of 2013 sales per Item, so Tableau needs Item in the view to calculate the correct aggregate measure. The second issue is the more complicated one, that we can't partition table calculations by other table calculations, for example to get the total sum of 2013 Sales for each of the top 80% and bottom 20% of 2013 sales per Item. So we have to build our own by looking at each row (Item) in the partition and making all needed calculations aware of what (fake) partition we are in. There are various ways to go about this, here's what I did:

             

            We need to pick two rows in the partition to generate totals for, to end up in the final display. I chose to use the last row of the 80% and the last row of the 20%.

             

            Then we need a way to identify those two rows. The last row (the last row of the 20%) is simple, it's always the row where LAST()==0. The last row of the top 80% is more complicated, for that I used the following Partition Row formula that returns that row's index (your Rank calculation):

             

            PREVIOUS_VALUE(WINDOW_MAX(IF [80/20] != LOOKUP([80/20],1) THEN [Rank] END))

             

            Starting from the inside on out, the IF statement checks to see whether the 80/20 measure will changed value in the next row, since the only place it will is for that last row of the top 80%, that returns the Rank to that one Row. The WINDOW_MAX then returns that to every other row in the partition, so we have it available for every Row, and finally the PREVIOUS_VALUE() is an optimization so the WINDOW_MAX is only computed once.

             

            Then there are a series of calculations that generate the needed totals, they are each set up to only return results for the two selected rows and Null for everything else. Here's the formula for the # of Items JTD:

             

            IF [Rank] == [Partition Row] THEN

                [Rank]

            ELSEIF LAST()==0 THEN

                [Rank]-[Partition Row]

            END

             

            This works because the level of detail of the view is the Item, and so we're just counting the rows in the partition instead of trying to use some sort of COUNTD() to directly count the items. The # of Items for the Top 80% is the current Rank, the # of Items for the bottom 80% is that row's Rank minus the Partition Row calculation I just described.

             

            For the % of Orders & Sales calcs, these use a similar format:

             

            IF [Rank] == [Partition Row] THEN

                WINDOW_SUM(SUM([2013 Orders]),FIRST(),0)

            ELSEIF LAST()==0 THEN

                WINDOW_SUM(SUM([2013 Orders]),FIRST()+[Partition Row],0)

            END

             

            For the Top 80%, the calc sums up the sales from the first row in the partition to the current row. For the bottom 20%, the calc sums the sales from the first row of the bottom 20% (the first row in our fake "partition") to the last row.

             

            Note that the Compute Using for all of these table calcs is on Item.

             

            You can see all of these in the 80/20 by Item JTD worksheet. Then I duplicated that worksheet build the 80/20 Recap JTD worksheet. I got rid of the measures that didn't need to be in this view, Ctrl+Dragged a copy of the 80/20 pill from the Color Shelf to the Rows Shelf, moved Item to the Level of Detail Shelf (if you haven't set the Compute Using of the table calcs to Item, you'll see them change results at this point), turned off the Tooltip for Item, and finally Ctrl+Dragged a copy of the # of Items JTD pill from the Measure Values card to the Filters Shelf and set it to filter for non-Null values. This gets rid of all extra whitespace and extra rows needed by Tableau to generate accurate totals that aren't needed in the final display.

             

            There are a couple of Ideas out there that could make this a whole lot easier, please vote them up if you'd like that too:

             

            Partitioning by Table Calculations: http://community.tableau.com/ideas/1194 - I think I made the case for this here.

            Top N% Option for Filters and Sets: http://community.tableau.com/ideas/2026 - if this existed, you could create a Set on the top 80% of sales, then use the In/Out of the Set as the dimension on Color and as the only dimension in the Recap view, and not need all those table calcs.


            Jonathan


            • 3. Re: Top N Bottom N Totals Only
              lynda.kroeger

              Thank Jonathan.  I am not seeing the calculations or the worksheet in the file you attached. 

              • 4. Re: Top N Bottom N Totals Only
                Matt Lutton

                This is one of those examples where having the version of a Tableau workbook automatically listed would benefit the forum SO MUCH.  It appears that your original workbook was in Version 8.0 and Jonathan's solution was in 8.1.  He'll now have to rebuild the entire thing from scratch, unless you can install 8.1 on your machine (you can have multiple version installed at the same time, so I'm not sure why some folks don't have 8.1 installed).

                 

                Sorry to interrupt, but this is becoming more and more of an annoyance for me!

                 

                Posters, you can help us help you by ALWAYS listing which version of the product you are using.

                • 5. Re: Top N Bottom N Totals Only
                  lynda.kroeger

                  I do have 8.0 and 8.1 versions on my machine.  I did open Jonathan's workbook in 8.1 and I did not see the worksheet or any of the calculations he mentioned in his post. 

                  • 6. Re: Top N Bottom N Totals Only
                    Matt Lutton

                    Odd, opens just fine for me. 

                    • 7. Re: Top N Bottom N Totals Only
                      lynda.kroeger

                      It opens fine for me too.  The issue is that what Jonathan has referenced in his post is not in the workbook.  I do not see a JTD worksheet in the Invoice - Sample-Revised-v2 jtd.twbx file he attached.

                      • 8. Re: Top N Bottom N Totals Only
                        Jonathan Drummey

                        Sorry about that, I'd attached a workbook from another forum post. I've revised my post with the correct workbook.

                         

                        Jonathan

                        • 9. Re: Top N Bottom N Totals Only
                          lynda.kroeger

                          Thank you so much Jonathan.  I thought I was losing my mind.  I really appreciate your efforts.

                          • 10. Re: Top N Bottom N Totals Only
                            Matt Lutton

                            I stand behind all my comments!  Regardless of whether this was the issue here, it is happening all the time and posters can make life much easier by listing what version they're on

                            • 11. Re: Top N Bottom N Totals Only
                              Jonathan Drummey

                              You're welcome! And Matt, I agree with you, posting versions would be

                              helpful.

                               

                               

                              On Thu, Feb 6, 2014 at 3:32 PM, lynda.kroeger <

                              • 12. Re: Top N Bottom N Totals Only
                                lynda.kroeger

                                Thanks Jonathan, this works great!  I will check out those other posts and vote on them. 

                                • 13. Re: Re: Top N Bottom N Totals Only
                                  lynda.kroeger

                                  Jonathan, 

                                   

                                  Your solution worked perfectly, just as you indicated it would.  I ended up having an additional request, which I thought could be easily accomplished with the partitioning you suggested.  However it is not working.  Basically, I am looking to indicate the items that fall into the bottom 20% that are in the Furniture department.  I created a calculated filter (bottom20department).  This states that if running % of total >.8 AND Department = Furniture then Include the record.  This works fine to filter the records and returns the corrects results(tab bottom 20 furniture). The detail is correct, but once again, I cannot get the recap to rollup correctly.  I created a tab with hard coded numbers to indicate the results (bottom 20 furniture recap tab).  I can't wrap my head around this to figure it out.  I've attached the revised workbook.  BTW, it is version 8.1.  Thanks!

                                  • 14. Re: Re: Re: Top N Bottom N Totals Only
                                    Jonathan Drummey

                                    Hi Lynda,

                                     

                                    My apologies for taking so long to get back to this, see the attached. In the recap worksheets you had put together, the Item dimension was missing. This meant that the sales aggregation was not being performed at the Item Level, so there would be only a single aggregation for the entire data set, and the table calculations would work over the entire data set. In addition, the Item needs to be in the view so that way it can get sorted by the sum of sales so the running total calculation can have the right order. Once the Item was back in the view, then I could build a couple of table calcs that did the filtering for the Bottom 20% of furniture items and sum up what was left to make the desired results. Because the view has a finer granularity (all of the items) than the display, the Compute Using of the table calcs is explicitly set to the Item (known as fixed addressing vs. the relative addressing of Table or Pane Down/Across), and I Ctrl+Dragged a copy of one of the measures onto the Filters Shelf to filter for non-Null values to get rid of the extra space imposed by Tableau for all the items that aren't displayed, and I turned off the tooltips on Item:

                                     

                                    2014-03-24 11_45_34-Tableau - items jtd v2.png

                                     

                                    Let me know if you have any questions!

                                     

                                    Jonathan

                                    1 of 1 people found this helpful
                                    1 2 Previous Next