9 Replies Latest reply on Jun 28, 2016 4:05 AM by Mattia Balzarini

    Segmentation in Percentile

    Mattia Balzarini

      Hello Tableau Community

       

      I'm facing an issue. I have a set of users and I need to segment them according to a measure.

       

      I want them segmented in percentile, according to the number of transactions they did or at least equally split. Let's say I have 1000 Users and I wanna be able to decide if I wanna them grouped each 100 (always according to number of transactions).

      I manage to have it in a static way, but I want a dynamic solution that would work with different set of Users and different measure (sometimes might be sales, sometimes margin and so on)

       

      Thanks in advance

       

      Mattia

        • 1. Re: Segmentation in Percentile
          Ashish Chaudhari

          Hi Mattia,

           

          can you draw some input and desired result from the input which can help in dummy data creation and building the solution?

           

          -Ashish

          • 2. Re: Segmentation in Percentile
            Mattia Balzarini

            Hi sure,

             

            I want the Userid segmenting as percentile based on Number of records. Result should be a chart with on x axis the percentiles and on column the count of users

            • 3. Re: Segmentation in Percentile
              Ashish Chaudhari

              Hi Mattia,

               

              Till the time I am working on this, you can refer to this link. It might give you an answer that you are looking for.

               

              https://www.interworks.com/blog/wjones/2015/08/14/percentile-distributions-dimension-tableau

               

               

              -Ashish

              • 4. Re: Segmentation in Percentile
                Ashish Chaudhari

                Hi Mattia,

                 

                Are you looking for something like this? Please find the attached below.

                 

                 

                I have created a calculated field as below.

                 

                IF [Number of records]<={FIXED :PERCENTILE([Number of records],.1)} THEN "<10th"

                ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.2)} THEN ">10-20th"

                ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.3)} THEN ">20-30th"

                ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.4)} THEN ">30-40th"

                ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.5)} THEN ">40-50th"

                ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.6)} THEN ">50-60th"

                ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.7)} THEN ">60-70th"

                ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.8)} THEN ">70-80th"

                ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],.9)} THEN ">80-90th"

                ELSEIF [Number of records]<={FIXED :PERCENTILE([Number of records],1)} THEN ">90th"

                END

                 

                Let me know if this helps.

                 

                -Ashish

                1 of 1 people found this helpful
                • 5. Re: Segmentation in Percentile
                  Mattia Balzarini

                  Yes I need something like you get. When I replicate it in my scenario something goes wrong. Don't understand why.

                  [Edit] Oh I guess it is because for you number of records is already aggregated for users, while for me it is not. I first have to calculate a column which gives me aggregation per user as in the article u linked

                   

                  Formula

                  IF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.1)} THEN "<10th"

                  ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.2)} THEN ">10-20th"

                  ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.3)} THEN ">20-30th"

                  ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.4)} THEN ">30-40th"

                  ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.5)} THEN ">40-50th"

                  ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.6)} THEN ">50-60th"

                  ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.7)} THEN ">60-70th"

                  ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.8)} THEN ">70-80th"

                  ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],.9)} THEN ">80-90th"

                  ELSEIF [Number of Records]<={FIXED :PERCENTILE([Number of Records],1)} THEN ">90th"

                  END

                   

                  Capture.PNG

                  • 6. Re: Segmentation in Percentile
                    Mattia Balzarini

                    Ok it is working great.

                    Now the next step issue is as follow. This works on the full set of customers. But if I use just a portion of them it doesn't recalculate the division.

                    How can I make it dynamic based on the group of users I use as base?

                    For example I divide customers in old and new and medium as in excel attached

                    • 7. Re: Segmentation in Percentile
                      Ashish Chaudhari

                      Hi Mattia,

                       

                      When you are taking tableau generated number of records its all falling in "<10th"  category since for every ID you are having value as 1. In the new data shared by you, do you want me to go with  Column C or you want me to use tableau generated number of record?

                      • 8. Re: Segmentation in Percentile
                        Mattia Balzarini

                        It is fine this way.

                         

                        The only problem I have now, is that this percentile seems to work based on the all users in my data set. If I filter them as for colums A. It doesn't seem to recalculate the percentiles for the smaller amount of users. Do you know how to make it dynamic to changes of the data set?

                        • 9. Re: Segmentation in Percentile
                          Mattia Balzarini

                          Any idea on how to make the percentile division being recalculated for different users filtering?

                           

                          Thanks

                          Mattia