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

# how many clients do how many purchase

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.

• ###### 1. Re: how many clients do how many purchase

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

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

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%)

Christophe

• ###### 4. Re: how many clients do how many purchase

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

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

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

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

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

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

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

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

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

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

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