3 Replies Latest reply on Oct 1, 2018 2:06 AM by lei.chen.0

# Calculate no of customers that have bought product A in each month and product B in past two months.

So basicaly I have a product A and a product B. I need to calculate the no of customers that have bought product A on a monthly basis along with product B min one in the last 2 months. For example if a customer bought A this month and B last month it should be counted. I managed to calculate the no of customers that have bought A and B in the same month but I don't know how to extend B to past 2 months. I have an idea using parameters for month but I need also to display a YTD monthly figures.

I'm new to tableau so any help would be highly appreciated.

• ###### 1. Re: Calculate no of customers that have bought product A in each month and product B in past two months.

However, solution might be different depending on the data format.

So please attach a workbook or some sample data, for more specific advices.

Besides, I have two questions about the requirements.

So basicaly I have a product A and a product B. I need to calculate the no of customers that have bought product A on a monthly basis along with product B min one in the last 2 months.

What's the definition of "on a monthly basis"?

Does it mean the customer phrases product A each month from the earliest date to the latest date in your data?

If the customer didn't purchase product A in any of the months, the customer will not be counted?

What's the definition of "last 2 months"?

Is it a relative date range from today or any selected month?

Regards

Lei

• ###### 2. Re: Calculate no of customers that have bought product A in each month and product B in past two months.

Thank you for picking this up Lei!

Please find attached an example sheet. At the moment the calculation is showing the no of customers that have bought both products in one month. I can see this at customer level and also at SR and SM level ( no of customers from each SR or SM area that meet the condition)

Now what i want:

For example In June customer C1 have bought product A but not B. However because B was bought in the last 2 months I need the customer to be counted also.

So for the condition to be true product A needs to be bought in the current month but B can be bought in current or previous month (current month -1). If outside current month -1 then not to be counted.

Hope it clarifies what I'm after.

Best regards,

• ###### 3. Re: Calculate no of customers that have bought product A in each month and product B in past two months.

Please refer to the attached workbook for details.

I don't think this solution is good because several table calculations are used, which makes it somewhat confusing.

However I haven't go any other idea for the moment

So please first have a look at the workbook to see if it meets your requirement.

Regards

Lei