3 Replies Latest reply on Dec 6, 2010 9:32 PM by Richard Leeke

    Plotting quartiles

    . charlie.morn

      Sorry if this has been posted before--the search function isn't working for me.

       

      I am on Tableau 6.0 and have a task I'd like to do related to plotting quartiles.

       

      Imagine I have a set of stores. Each quarter, I figure out how much profit that store has made. At each quarter, I would like to plot the following values in a line chart: the maximum profit of any store, the bottom of the top quartile, the median, the top of the bottom quartile, and the lowest profit any store made.

       

      I've been fooling around with this for a while and have run a calculated field that correctly gets me the quartiles for each value by the following:

       

      5-RAWSQLAGG_INT("NTILE(4) OVER(ORDER BY AVG(%1))", [ResultsValue])
      . The problem is, when I try to push this to a graph, it recalculates the quartiles because of the summarization that happens, making everything in the 4th quartile. Unfortunately, the database does not have the quartile information built into records, so this will have to be done either by the query or the Tableau engine. Has anyone done something like this before?

        • 1. Re: Plotting quartiles
          Richard Leeke

          I do lots of analysis with percentiles - and it's unfortunately harder than I'd like, but it is doable.

           

          I wrote up a long description of the options I could find a while ago here (everything I said for percentiles applies to quartiles too):

           

          http://www.tableausoftware.com/forum/calculating-percentiles-rawsql-or-custom-sql-connections

           

          At that time the answer was if you are using Oracle it's easy, otherwise it's really hard.  I did manage to get an example going with SQL Server using ntile() (or similar) - but it was ugly.

           

           

          With version 6 you can calculate the values in Tableau with Table Calculations.  I posted the details of how to do that for inclusion in the Tableau Calculation Reference Library that James is working on.

           

          http://www.tableausoftware.com/forum/tcrl:-quantile

           

           

          It's also worth noting that Tableau directly supports median() as an aggregate function if you are using Oracle or the version 6 extracts with the Tableau Data Engine.  You could add your voice to the lobby group for that to be extended to quantiles in general for databases that support it (Oracle and the Data Engine).

          • 2. Re: Plotting quartiles
            . charlie.morn

            Thank you! This is very comprehensive and I really appreciate the help.

             

            I'm using MSSQL, so I've used NTILE() like I mentioned, but I've not been able to figure out how to get beyond that. Extracts are probably not doable either simply because of how much data I have to work with. Thanks again for all your help.

             

            *Edit* In other news, I used your tableau file from that link and tried to replicate your results for a good hour and couldn't... my graphs were always wrong. Then, I realized that the axes were wrong! Everything works great now and I've moved it over to my live dataset. Thanks again. */Edit*

            • 3. Re: Plotting quartiles
              Richard Leeke

              Glad it was useful.

               

              The NTILE() approach is really tricky.  The way to achieve percentiles with NTILE() in SQLSERVER is explained in a link on some SQLSERVER site that I linked to in the first of my postings above.  But as I mentioned, the full solution given there didn't work from Tableau because it used CTEs, which don't work from a custom SQL connection.  I did manage to get some horrible intermediate kludge going, but unless your data volumes are absolutely enormous I think the Table Calc approach is much better.