5 Replies Latest reply on Mar 29, 2016 7:31 AM by Derrick Austin

# Filtering by number of purchases per customer by month.

I have a list of job requests. Each request has a job ID and a member ID (which relates to the customer who made the request). The data also has the date of the request.

I am trying to show what proportion of customers made 6 or more requests in a given month. When I filter, tableau seems to filter those who made 6 or more requests in the whole data set, rather than looking at each month in isolation..

There must be a simple way to do this?

• ###### 1. Re: Filtering by number of purchases per customer by month.

Hi Thomas.

Your solution sounds like it would need a Level of Detail (LOD) calculation but without seeing data I can't be 100% sure.

So you would need to create a custom date to be in your LOD calculation. This blog post explains how to create a custom date but instead of years you would want months - http://www.thedataschool.co.uk/lorna-eden/ds2tipweek-lods-of-fun-with-custom-dates/

Your LOD would then look like this: { FIXED date(Months) : sum(Number of records)}

This would then sum the number of records based on each month. If your not sure about number of records you could use count(CustomerID).

This LOD could then be used on a filter where you would choose at most 6.

I hope this helps. If it doesn't then if you can send a Tableau Workbook with a sample dataset in and I can look at it too see what would work.

Thanks

Lorna

• ###### 2. Re: Filtering by number of purchases per customer by month.

Hi, I presume you have month on rows or columns? If so, then it may be as simple as adding context filters so Tableau knows how to apply it.... can you share the workbook?

• ###### 3. Re: Filtering by number of purchases per customer by month.

Hey Thomas,

It is hard to say without looking at your data, but I would guess you could use an LoD calculation.

First, rollup a calculated field to month from date granularity:

DATETRUNC('month', [CalDt])

Then, LoD it:

{FIXED [Month] : COUNTD([PurchaseId])}

Then, filter on this field.

Hope this helps!

- Derrick

• ###### 4. Re: Filtering by number of purchases per customer by month.

Thanks guys, I can't quite make sense of how your proposed solutions would work. I've tried them out but to no avail so I thought I'd make it more clear....

I have hundreds of thousands of rows of data like this
JOBID Member ID Date of request

0145    33567               2nd march 2015

0322     33112               4th April 2015

0436      33567               30th march 2015

0891      38999               10th April 2015

As you can see, some members make more than one request in a given month (member 33567 made at least two requests in march 2015).

I have screenshot below what I have, which the number of members that have made a request that month. What I want to show is how many of those members made only one request, how many made more than 4 etc etc.

Derek, if I filter with that LOD, won't I just be filtering on number of jobs per month, whereas what I need is to filter on number of jobs per member per month?

Rod, for context filter to work, don't I need more than one filter? I only need to filter for members with more than 4 jobs in that month..

Thanks

• ###### 5. Re: Filtering by number of purchases per customer by month.

Hey Thomas,