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

    Help calculating dynamic quartiles

    Keir Blockley

      Hi

       

      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

       

      Keir

        • 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

          END

           

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

           

          Adding a secondary table calculation for reassurance:

           

          Hope this helps!

           

          Bryce

          • 2. Re: Help calculating dynamic quartiles
            Keir Blockley

            Bryce

             

            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.

             

            Thanks

             

            Keir

            • 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

                Bryce

                 

                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.

                 

                Keir

                • 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

                  END)}

                   

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

                   

                  Bryce

                  • 6. Re: Help calculating dynamic quartiles
                    Keir Blockley

                    Bryce

                     

                    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.

                     

                    Keir