6 Replies Latest reply on Jun 11, 2018 8:51 AM by Keir Blockley

    Help calculating dynamic quartiles

    Keir Blockley



      Please can someone help with calculating quartiles. The quartile would be based on the "hourly rate" column, but the quartile that a row is in may change if the "team" filter is used.


      I have attached a workbook with an example data set.


      I would like to be able to do a percentage split by gender within each quartile.


      Please see below picture showing the values for quartiles, first table for "all teams" and then for "team A"


      gpg example.png


      Thanks in advance



        • 1. Re: Help calculating dynamic quartiles
          Bryce Larsen

          Hi Keir Blockley

          Could you save this in 10.2 or 10.3? I'm unfortunately not on the newer version of tableau yet. Or you could post the data as well.


          Having said that, you could look to utilize the PERCENTILE() function.


          Example from Superstore. I created this Calculated Field and converted to Dimension:

          IF [Sales] <= {fixed: PERCENTILE([Sales], .25)} THEN 1

          ELSEIF [Sales] <= {fixed: PERCENTILE([Sales], .5)} THEN 2

          ELSEIF [Sales] <= {fixed: PERCENTILE([Sales], .75)} THEN 3

          ELSEIF [Sales] <= {fixed: PERCENTILE([Sales], 1)} THEN 4



          Then I simply put on Number of Records and changed to Running Total:


          Adding a secondary table calculation for reassurance:


          Hope this helps!



          • 2. Re: Help calculating dynamic quartiles
            Keir Blockley



            Yes it has helped thank you.


            Is there a feature in tableau to make the quartiles have an even amount of records? I can understand why it has apportioned the records as it has though, as there are multiple records with the same hourly rate, so it doesn't "neatly" fit into 4 quarters.





            • 3. Re: Help calculating dynamic quartiles
              Bryce Larsen

              Hmm. Sorry, I'm not really sure I follow. What do you mean an "even" amount of records? Can you provide an example?


              And glad it at least got you started!

              • 4. Re: Help calculating dynamic quartiles
                Keir Blockley



                I meant that so that each quartile has an equal amount of records within each one e.g. if there are 400 records in total, there would be 100 in each quartile.


                The issue is that, as my data has lots of records with the same hourly rates, it doesn't split the quartiles evenly.



                • 5. Re: Help calculating dynamic quartiles
                  Bryce Larsen

                  Ah, I see. This is difficult and not sure I would really refer to these as 'quartiles' then, at least not in the statistical sense.


                  Unfortunately there's no level of detail expression for Rank. You can do it on the view if showing all records, but you can't store it in the data. I might suggest doing this prior to bringing it in. If you can sort your data by Hourly Rates and assign IDs in this way (by Gender if desired), then you could do it in that way.


                  {fixed [Gender]: MAX([ID])} returns Max ID in the data per each gender. And then you can do something like:


                  {fixed [Gender]: MAX(

                  IF [ID] < [Max ID per Gender]*.25 THEN 1

                  ELSEIF [ID] < [Max ID per Gender]*.5 THEN 2

                  ELSEIF [ID] < [Max ID per Gender]*.75 THEN 3

                  ELSE 4



                  That might work and return the group per each record by gender.



                  • 6. Re: Help calculating dynamic quartiles
                    Keir Blockley



                    I don't think I can do the above, as the dashboard will have multiple filters, which will change which quartile a record will be in potentially. I think I am expecting too much for it to do the quartiles perfectly.


                    Thanks for your help with this, I am using the solution in your original response.