5 Replies Latest reply on Sep 13, 2016 4:10 AM by Sunny Side

    Computing and dividing Sales as Quartiles

    Ankit Sharma

      Hi all,

       

      I'm new to Tableau community, so forgive if I'm direct in my asking.

       

      I have 2 columns- one with an SKU item and other with a dollar value (sales of a product). I need to create 4 slabs - Top, Second,Third and Bottom Quartiles. And finally color code them with different Quartiles displaying different color. I have attached an excel sheet displaying the sample format I'm trying to create in Tableau.

       

      I looked at various discussion on Tableau community but no fruitful result yet.

       

      Please let me know if there is a way to do this.

       

      Thanks

        • 1. Re: Computing and dividing Sales as Quartiles
          Dan Sanchez

          Hi Ankit!

           

          Welcome to the forums!

           

          One of the great things about Tableau is how quickly we can add Quartile analysis to a view without needing to do much in terms of writing calculated fields.  For this one in particular, we can use the Analytics pane exclusively with no calculations required at all!

           

          I started by building out a bar chart with your data like this:

           

          Next you'll want to click the Analytics Pane towards the top left and drag out the Distribution Band onto the Table option:

           

          This will launch a configuration window where you can select Quartile in the Value drop down (set the Quantile value option to 4 to get quartiles), change the Label drop down to Value, and then enable to Fill Above and Fill Below options.  I also added a Stoplight fill color:

           

           

          And there we go!  Now we can quickly see which of the products fall in which quartile, as well as the value associated with each quartile.  Hope this helps get you started on your analysis!

           

          Thanks!

          • 2. Re: Computing and dividing Sales as Quartiles
            Ankit Sharma

            Hi Dan,

             

            Thank you for such detailed explanation. Really Appreciate it

             

            Unfortunately, it doesn't serve my purpose. My client simply needs a tabular format with columns mentioning Quartiles in front of a particular description. I an use this approach in later phase maybe.  Also, I have to compare other parameters as well (around 10) and put them in a tabular format hence the Tabular choice. I did found one interesting link which uses dynamic grouping that almost solved my problem:

             

            Link:
            Tableau Public

             

            I used the rank percentile with nested If's to get this format :

            Capture 1.JPG

             

            I used the following formula in "Adj. Sales Quartile" :

            IF [Rank Percentile of $ Sales]>= 0.75 THEN "Top Quartile"

            ELSEIF [Rank Percentile of $ Sales]<= 0.75 AND  [Rank Percentile of $ Sales]>0.5 then "Second Quartile"

            ELSEIF [Rank Percentile of $ Sales]<= 0.50 AND [Rank Percentile of $ Sales]>0.25 then "Third Quartile"

            ELSEIF [Rank Percentile of $ Sales]<= 0.25  then "Bottom Quartile"

            END

             

            where "Rank Percentile of $ Sales" is simply :

            RANK_PERCENTILE(SUM([ACV Adj $ Sales (12 Week)]))

             

            The problem I'm facing is when I use the filter to look at the particular stock code, the above formula only consider that item for computation of Quartile hence always return 'Top Quartile' . In the image below, I searched for a category of product and I get around 20 of them...because it saw 20 of their SKUs and ranked them.. so top 5 will get "TOP Quartile".. which is wrong as I want to know the position of Shea in total.

             

            Please Ignore the other columns.

            Capture3.JPG

             

             

            If you could help me on this, that'll be great !!

             

            Thanks
            Ankit

            • 3. Re: Computing and dividing Sales as Quartiles
              Dan Sanchez

              Hi Ankit!

               

              I think we may just need to create a different field to filter with instead of using a quick filter on [Description], kind of like is outlined in this KB article:

               

              Filtering the View Without Filtering Underlying Data | Tableau Software

               

              Can try testing out the following formula and filtering with it to see if this does the trick?

               

              LOOKUP(MIN([Description]), 0)

               

              This type of calculated field is a trick we can use to "hide" rows of data rather than completely filtering them out of the view which is great when we are leveraging Table Calculations.  This means that the "hidden" rows are still there in the background which allows the table calculations to compute across all of them, thus returning the correct quartile values for you.

               

              One caveat around this is that if the text table you are building is really large in height and/or width, you may see some slower load times in terms of performance.

               

              Thanks Ankit!

              • 4. Re: Computing and dividing Sales as Quartiles
                Ankit Sharma

                Hi Dan,

                 

                Thanks. It worked :-)

                • 5. Re: Computing and dividing Sales as Quartiles
                  Sunny Side

                  Hi Dan,

                   

                  Sorry to reopen the case. Let me know if I shall reopen it as a separate case.

                   

                  The above filtering did work but I'm not able to get the correct Quartiles until I use the existing [Description] with the new custom [Description*] in the same Row. I don't want the [Description] column.

                  Description 2.JPG

                   

                  If I simply use the custom [Description*], my records are reduced to 20 something.

                  Description.JPG

                   

                   

                  Would you know what is happening ?

                   


                  Thanks,

                  Ankit