8 Replies Latest reply on Jan 31, 2018 12:26 PM by Giorge Luiz

# How to see % of clients buying in current year and last year

Hi!

My client wants to see the % of clients who bought in current year and also bought in last year.

I achieve this with LOD and parameters, but he want's to see it dynamically in a grid using data on columns.

Here's an example of my calculation:

SUM(

IF

{FIXED [Customer ID] : COUNTD(IF YEAR([Order Date]) = 2011 THEN [Order ID] END)} > 0

AND

{FIXED [Customer ID] : COUNTD(IF YEAR([Order Date]) = 2012 THEN [Order ID] END)} > 0

THEN 1

ELSE 0

END)

/

MAX({COUNTD(IF YEAR([Order Date]) = 2011 THEN [Customer ID] END)})

I think I could use table calculations, but I'm not seeing a way of doing this without parameters.

Can anyone help me?

• ###### 1. Re: How to see % of clients buying in current year and last year

Hi

see the attached - you need to create an lod to id customers that purchase in each year and then use that in a table calculation to look at successive years of purchase

first - i used count of order id in superstore

then

you can then sum that value using window-sum

it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: How to see % of clients buying in current year and last year

Thanks for your reply but it didn't solve my problem.

I need to create a sheet like this:

I can't create a grid with the customers names on rows.

Any suggestion?

• ###### 3. Re: How to see % of clients buying in current year and last year

Just divide by the total for each year

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: How to see % of clients buying in current year and last year

This isn't the correct number.

Using my original calculation:

SUM(

IF

{FIXED [Customer ID] : COUNTD(IF YEAR([Order Date]) = 2011 THEN [Order ID] END)} > 0

AND

{FIXED [Customer ID] : COUNTD(IF YEAR([Order Date]) = 2012 THEN [Order ID] END)} > 0

THEN 1

ELSE 0

END)

/

MAX({COUNTD(IF YEAR([Order Date]) = 2011 THEN [Customer ID] END)})

The result is 43,69% and I already confirm it.

Using the calculation below:

COUNTD customer

{ FIXED [Customer Name],year([Order Date]):countd([Order ID])}

window sum count

(window_sum(

if LOOKUP(min([COUNTD customer]),0) >0 and

LOOKUP(min([COUNTD customer]),1)>0 then 1 else 0 end ))

/

SUM({fixed year([Order Date]) : countd([Customer Name])})

I get the result below:

Which is not correct.

• ###### 5. Re: How to see % of clients buying in current year and last year

Hi Giorge,

Are you looking for something like this in attached image ?

Please let me know if this helps out

Regards

Raja Usman Ali

1 of 1 people found this helpful
• ###### 6. Re: How to see % of clients buying in current year and last year

Hi Usman,

I think your solution will solve my problem.

Can you attach your workbook so I can see the how did you get there?

Thank you!

• ###### 7. Re: How to see % of clients buying in current year and last year

Hi Giorge,

Please have a look on attached workbook developed in Tableau Desktop 10.4

Regards

Raja Usman Ali

1 of 1 people found this helpful
• ###### 8. Re: How to see % of clients buying in current year and last year

Thank you, Usman.

It's not the exactly same thing that I was trying to achieve but it worked pretty well. My client liked it.

Giorge Caique