14 Replies Latest reply on Sep 5, 2016 1:40 PM by TVTY BI

    how many clients do how many purchase

    TVTY BI

      Hi guys,

       

       

      I'm a newbie in tableau,  it's my first post  and ... sorry for my english.number of client as a function of number of purchase.png

      I did the following chart (please find attached):

      Here is how read the chart:

      x axis = number of client

      y axis = number of purchase

       

       

      so in this case we can say that:

           21 clients did 1 purchase

           8 clients did 2 purchases

           2 clients did 3 purchase

           1 client did 4 campaign

           1 client did 6 campaign

       

       

      but what i want is to have (for the following value of the x axis):

           100% for 1        (21+8+2+1+1)/(21+8+2+1+1)*100

           36% for 2         (8+2+1+1)/(21+8+2+1+1)*100

           12% for 3         (2+1+1)/(21+8+2+1+1)*100

            6% for 4

       

       

      I don't show you which calculation i did because i think we can deduct from the view itself. I was wondering if a table calculation could be a solution.

       

       

      Thanks for your help

        • 1. Re: how many clients do how many purchase
          Luciano Vasconcelos

          This is very easy:

           

          Click table calculation(mine is in portuguese):

           

           

          Set this option and it's ready.

          • 2. Re: how many clients do how many purchase
            TVTY BI

            Sans titre 2.png

            Thanks Luciano but if I do this I don't obtain what i'm searching for. Find attached what I obtained.

             

            I want:

             

            "

            for the following value of the x axis:

                 100% for 1        (21+8+2+1+1)/(21+8+2+1+1)*100

                 36% for 2         (8+2+1+1)/(21+8+2+1+1)*100

                 12% for 3         (2+1+1)/(21+8+2+1+1)*100

                  6% for 4

            "

            • 3. Re: how many clients do how many purchase
              TVTY BI

              Please, find attached a tableau workbook that simulate the problem.

              I obtain following results in the sheet 1:

               

                   4 Clients did 1 purchase

                   2 clients did 2 purchases

                   1 clients did 3 purchases

               

              I want obtain:

               

              100% for the first bar (4+2+1)/(4+2+1)

              42% for the second bar (2+1)/(4+2+1)

              14% for the third 1/(4+2+1)

               

              What I want is something like this:

               

              % of client with at least 1 purchase (100%)

              % of client with at least 2 purchases (42%)

              % of client with at least 3 purchases (14%)

               

              Thanks for your help,

              Christophe

              • 4. Re: how many clients do how many purchase
                Luciano Vasconcelos

                I don't have your Tableau version.

                Post a sample of your data in a xls file.

                • 5. Re: how many clients do how many purchase
                  TVTY BI

                  here are the xls and a screen shot

                   

                  Purchase number by client :

                  {fixed [Client]: COUNTD([Purchase])}

                   

                   

                  Purchase number by client (bin):

                  size of bins:1

                   

                  Thanks!

                  • 6. Re: how many clients do how many purchase
                    Kashish Chauhan

                    Hi Christophe,

                     

                     

                    I have tried to do this and even got to the solution. I don't know whether this is the right way of doing it.

                     

                    First of all for counting the purchases create the following calculated field:

                     

                     

                    Then create another calculated field for the percentage:

                     

                     

                     

                    This is the final output:

                     

                     

                     

                     

                     

                    Thanks,

                    Kashish Chauhan

                    • 7. Re: how many clients do how many purchase
                      TVTY BI

                      thanks Kashish, 

                       

                      but I don't want static values for percentages but I want to obtain percentages with calculation.

                      Indeed, the excel and the tableau workbook attached before is just a little proof of concept.

                      After I want to apply this case on another file with thousands row

                       

                      Thank you

                      • 8. Re: how many clients do how many purchase
                        Akriti Lal

                        Hi Christophe,

                          As i understand your question, what you need to do is simply go at the Top in Tools Menu and click on

                         

                        Analysis --> Percentage Of --> Table  ( See screen below )

                         

                        As a result you will get the result in Percentage as you are expecting.

                        • 9. Re: how many clients do how many purchase
                          Andrew Watson

                          Based on your Excel, assuming that's what your data looks like behind the scenes, do the following:

                           

                          Create a calculated field NumberOfClients: { FIXED : COUNTD([Client])}

                           

                          Create a calculated field to calculate the %: SUM([Number of Records])/SUM([NumberOfClients])

                           

                          Gives you the following output:

                           

                          • 10. Re: how many clients do how many purchase
                            Kashish Chauhan

                            Hi Andrew,

                             

                             

                            Your solution however is showing the percentage of purchases i.e. purchase 1 contributes 100 % and so on. Christophe has a different approach. Are you able to achieve the same percentage client wise? I tried but I am not able to do it client wise.

                             

                             

                             

                            Regards,

                            Kashish Chauhan

                            • 11. Re: how many clients do how many purchase
                              Andrew Watson

                              I went off these requirements:

                               

                              I want obtain:

                               

                              100% for the first bar (4+2+1)/(4+2+1)

                              42% for the second bar (2+1)/(4+2+1)

                              14% for the third 1/(4+2+1)

                               

                              What I want is something like this:

                               

                              % of client with at least 1 purchase (100%)

                              % of client with at least 2 purchases (42%)

                              % of client with at least 3 purchases (14%)

                               

                              I wasn't able to open any of the attached Tableau workbooks so had to base my calculations off the Excel that was attached.

                               

                              Based on the number of responses to this if no one has interpreted what is wanted correctly yet, which may or may not be the case, a rewording of the problem may be required.

                              • 12. Re: how many clients do how many purchase
                                TVTY BI

                                Andrew,

                                 

                                As Kashish said, it's not exactly what I want. I will try to better explain.

                                 

                                Let’s consider a new excel example with no repetition of a purchase id (we don’t care the kind of purchase). The previous example (with repetition of purchase number or id) was ambiguous and led us to our misunderstanding

                                 

                                In your solution for the previous example, you found that

                                 

                                - 100% did the purchase 1

                                - 42% did the purchase 2

                                and so on

                                 

                                You found: « how many clients have we got for each kind of purchase »

                                 

                                I would have better chosen different names for each purchase.

                                 

                                What I want is "how many percent of client did x purchases or plus"

                                 

                                In my case, I want to get the following results:

                                 

                                100% of clients did one purchase or plus (all client did at least one purchase)

                                42% of clients did 2 purchases or plus (client 1, client 6 and client 7)

                                14% of clients did 3 purchase or plus (only the client 1)

                                 

                                i.e. the pourcentage of clients vs the purchase number (or plus)

                                 

                                I hope this make sense

                                • 13. Re: how many clients do how many purchase
                                  Andrew Watson

                                  I think I've got it...this was a tricky one...

                                   

                                  First the easy bit, you need to create the bins for the Number of Purchases - in your example this is 1 purchase, 2 purchases or 3 purchase.

                                   

                                  NumberOfPurchases: { FIXED [Client]:COUNT([Purchase])}

                                   

                                  On creation this will be a measure - drag it to Dimensions as we want to use this as a dimension - each value will be a different bar.

                                   

                                  We also require a fixed denominator, the Total Clients.

                                   

                                  TotalClients: { FIXED : COUNTD([Client])}

                                   

                                  Leave this as a measure and now the complex parts begin. We need to create the numerator. In your example when 1 purchase all clients need to be counted, as they all made at least 1 purchase. For 2 purchases all clients who have made 2 or more purchases need to be counted, etc.

                                   

                                  To begin let's get the distinct count of all clients in each group, a simple distinct count. Referring to your example 4 had 1 purchase, 2 had 2 purchases and 1 had 3.

                                   

                                  Number Of Clients: COUNTD([Client])

                                   

                                  These are the calculations required for each bin in your example:

                                   

                                  100% for the first bar (4+2+1)/(4+2+1)

                                  42% for the second bar (2+1)/(4+2+1)

                                  14% for the third 1/(4+2+1)

                                   

                                   

                                  We have the denominator from the TotalClients - which is a fixed 7 clients. We also have the following for each bin:

                                   

                                  Somehow we need to get the number 7 as the numerator for bin 1, 3 as the numerator for bin 2 and 1 for bin 3. This is the tricky bit. We already have the total clients, 7, and know we just need to disregard the NumberOfClient from the previous bins - i.e. for bin 1 we keep all clients (disregard the previous, which is none), for bin 2 we disregard the 4 clients from bin 1, for bin 3 we disregard the clients from bins 1 and 2.

                                   

                                  The LOOKUP function allows us to get the value from the previous bin.

                                   

                                  LOOKUP: ZN(LOOKUP( [NumberOfClients],-1))

                                   

                                  Note the use of ZN. This gives a 0 for bin 1 where there isn't a previous value to return for the NumberOfClients.

                                   

                                  This works fine for bin 2 but for bin 3 we disregard clients from bins 1 and 2. This is where the RUNNING_SUM comes into play.

                                   

                                  RUNNING: RUNNING_SUM([LOOKUP])

                                   

                                  Now it should be clearer where to go from here - if you recall for bin 1 we wanted a numerator of 7, for bin 2 we wanted 3 and for bin 3 we wanted 1. A simple TotalClients minus RUNNING will provide those numbers.

                                   

                                  Numerator: SUM([TotalClients]) - [RUNNING]

                                   

                                  The final step is to calculate the %, the numerator minus the demoninator.

                                   

                                  PCT: [Numerator]/SUM([TotalClients])

                                   

                                  Put into a bar chart:

                                   

                                  Hopefully that a) makes sense (not easy to describe) and b) is what you're looking for.

                                  1 of 1 people found this helpful
                                  • 14. Re: how many clients do how many purchase
                                    TVTY BI

                                    Hi Andrew and everyone,

                                     

                                    Great! This is exactly what i was looking for. Thanks a lot. I understood well your solution.

                                    You have no idea of how much it helps me.

                                     

                                    Christophe