11 Replies Latest reply on Feb 19, 2017 6:53 AM by Norbert Maijoor

    Top 80% Reporting

    Ravindran Venugopal

      My requirement is quite different from the various solutions present here on Top n percent reporting.

       

      I have a table like shown below:

       

      Territory
      Customer
      Sales Value
      NorthCust-1809089
      NorthCust-2907097
      ...
      EastCust-149087
      EastCust-15789787
      ...
      SouthCust-291223422
      SouthCust-20324558

       

       

      I can get a list of customers contributing to top 80% of sales by listing customer-wise in the descending order of Sales Value and applying a calculated boolean field

           RUNNING_SUM(SUM(Sales Value))<=TOTAL(SUM(Sales Value))*80/100

      as a filter.

       

      My requirement is a list of Territories with total of sales from Customers who contribute to Top 80% of sales. Please note, it is not list of Territories THAT contribute to Top 80% of sales.

       

      Please find attached some sample data (with a different set of dimensions and metrics but on same principle). I want the list in Sheet 4 to only have "Producer" but with sum of Clicks of Creatives that contribute to top 80% of clicks.

        • 1. Re: Top 80% Reporting
          Norbert Maijoor

          Hi Ravindran,

           

          Not sure but find my approach as reference below and stored in attached workbook version 10.0 located in the original threat.

           

           

          Upfront, thanks for your feedback.

           

          Regards,

          Norbert

          1 of 1 people found this helpful
          • 2. Re: Top 80% Reporting
            Ravindran Venugopal

            Dear Norbert,

             

            Thanks for your immediate response. I too tried something on similar lines as you have. Unfortunately it does not produce the way I want the output.

             

            I want the output without "Creative" column .. but the sum of creative as shown in your output should reflect against the respective "Producer" ... If we simply remove Creative column the RUNNING_SUM simply goes by the Producer ignoring Creative ...

             

            Any thoughts?

             

            Regards

            Ravindran

            • 3. Re: Top 80% Reporting
              Norbert Maijoor

              Goodmorning Ravindran,

               

              Hmmm. I do not understand why you would "vizualize" as described. Could you elaborate?

              • 4. Re: Top 80% Reporting
                Ravindran Venugopal

                Dear Norbert,

                The sample data is not my real data. My real data is somewhat similar to the table I had shown in my first post. The solution is for a consumer goods company which sells its goods through over thousands of retailers (stores). For analysis we want to consider only those retailers who contribute to 80% of the total sales. This in a simple excel is achieved by computing the contribution of each retailer a.k.a salience or share of total sales which is "retailer sale / total sales" and arriving at the cumulative percent sorting the retailers in the descending order of the contribution. Once we identify those retailers then all subsequent analysis and visualizations are at levels higher than retailer. Namely, their geographical classification or trade classification etc.,

                 

                I hope I was able to convey my requirement clearly ...

                 

                Regards

                Ravindran

                • 5. Re: Top 80% Reporting
                  Norbert Maijoor

                  Good afternoon Ravindran,

                   

                  Based on your elaboration find my approach below. I am not sure if i interpreted it correctly.

                   

                  • 6. Re: Top 80% Reporting
                    Ravindran Venugopal

                    Dear Norbert,

                     

                    I have attached an Excel sheet showing my requirement ... please take look ...

                     

                    Regards

                    Ravindran

                    • 7. Re: Top 80% Reporting
                      Norbert Maijoor

                      Hi Ravindran,

                       

                      Oke I now understand what you are aiming for but can't get "my head around" how to keep the total amount despite the selection on the retailers up till the running_sum of 80% and sure i am missing something. Would like ask my fellow ambassador Simon Runc to the table.

                       

                      Simon Runc We need the total of 96,50 in sheet Trade class an Geo Glass as basis for the calculations in this sheet. As been a long week and can't figure it out anymore,Hope you  can give some "light" in the tunnel;)

                       

                      • 8. Re: Top 80% Reporting
                        Jonathan Drummey

                        How's this?

                         

                        Screen Shot 2017-02-18 at 12.35.12 PM.png

                         

                        In building this out I rewrote some calculations and simplified them, in addition I removed all LOD expressions because they are not necessary. There are four complications in building out this view:

                         

                        1) The only way presently to get a top N % is with table calculations. (Yes, there are potentially hacks with nesting LOD expressions but they don't scale). Vote up https://community.tableau.com/ideas/2026 to get a top N % option for filters and sets. In any case, this is the core issue because table calculations add complexity.

                         

                        2) Table calculation filters are only applied *after* other table calculations are computed, so to effectively filter the Trade class and Geo class views the "filters" need to be implemented as nested calculations. For example here's the formula for the Sales to Keep measure that is effectively doing a SUM(IF... where inner IF is implementing the filter for the Top 80%.

                         

                        IF FIRST()==0 THEN

                            WINDOW_SUM(IF [% of Total Retailer Cumm] <= [% to keep]

                                THEN

                                    SUM([sales])

                            END)

                        END

                         

                        3) Table calculations only work on the dimensions in the view, they can't work at a finer grain than the view. Therefore the retailer dimension *must* be in the viz level of detail (vizLOD) in order to compute what is in the top 80% and what isn't. This means that calcs like the Sales to keep end up nesting on top of the top 80%. Since the calcs need to compute in different directions you'll need to pay careful attention to how they are nested.

                         

                        4) We can't sort dimensions based on table calculations. Therefore to do the sort in the Trade & Geo views I used the ad hoc pill technique I wrote about in Tableau Padawan: Faster Nested Sorts — DataBlick.

                         

                        5) In the original dashboard there was a Filter Action set up. Filter Actions are applied as dimension filters, and dimension filters are computed *before* table calculations are computed. This would remove values that are needed for the calcs to work, so in the Dashboard jtd sheet I only used Highlight Actions which do not filter results.

                         

                        If you want to filter the Trade class and/or Geo class sheets then you'll need to use a table calculation like LOOKUP(ATTR([Trade Class]),0).

                         

                        v9.3 workbook is attached, let me know if you have any questions!

                         

                        Jonathan

                        3 of 3 people found this helpful
                        • 9. Re: Top 80% Reporting
                          Tharashasank Davuluru

                          Thanks Jonathan for sharing this workaround and useful information. I learned a lot Through this thread.

                          • 10. Re: Top 80% Reporting
                            Ravindran Venugopal

                            Thanks a lot Jonathan Drummey,

                             

                            Thanks to Norbert and Simon too...

                             

                            I all, I have learnt a lot from all from this thread ..

                             

                            I was about to address this requirement by introducing a rank column in my base table with SQL and then using it.

                             

                            Most Marketing managers (not Sales Managers) look at only a percent of their total customer base as their loyal customer base and plan marketing strategy around them .. this is a very common requirement ..

                             

                            I wonder why has Tableau not addressed it (like there is an way to list Top 'N') ... I hope such a feature will be available in the coming versions of Tableau

                             

                            Regards

                            Ravi

                            • 11. Re: Top 80% Reporting
                              Norbert Maijoor

                              Goodmorning Jonathan,

                               

                              Thanks for stepping in. As mentioned by others. Learned a lot!:)