
1. Re: how many clients do how many purchase
Luciano Vasconcelos Sep 2, 2016 8:55 AM (in response to TVTY BI) 
2. Re: how many clients do how many purchase
TVTY BI Sep 2, 2016 9:12 AM (in response to Luciano Vasconcelos) 
3. Re: how many clients do how many purchase
TVTY BI Sep 2, 2016 9:49 AM (in response to 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 Sep 2, 2016 9:57 AM (in response to TVTY BI)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 Sep 2, 2016 11:21 AM (in response to Luciano Vasconcelos)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!

Sans titre 4.png 414.8 KB


6. Re: how many clients do how many purchase
Kashish Chauhan Sep 3, 2016 4:40 AM (in response to TVTY BI)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 Sep 4, 2016 3:37 AM (in response to 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 Sep 3, 2016 5:16 PM (in response to TVTY BI) 
9. Re: how many clients do how many purchase
Andrew Watson Sep 3, 2016 5:45 PM (in response to TVTY BI) 
10. Re: how many clients do how many purchase
Kashish Chauhan Sep 3, 2016 10:39 PM (in response to Andrew Watson)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 Sep 4, 2016 3:03 AM (in response to Kashish Chauhan)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 Sep 4, 2016 10:13 AM (in response to Andrew Watson)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 Sep 4, 2016 6:07 PM (in response to TVTY BI)1 of 1 people found this helpfulI 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.

14. Re: how many clients do how many purchase
TVTY BI Sep 5, 2016 1:40 PM (in response to Andrew Watson)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