4 Replies Latest reply on Jun 1, 2017 2:40 AM by Benjamin Moerman

# Distinct count based on x previous periods

Hi,

I've already read dozen of posts with a similar name but nothing seems to work.

I would like to make a distinct count of customers based on a rolling Period.

Let's use the Superstore database and try to have rolling 3 rolling month data (I will replace that 3 by a parameter then)

I would like to see a month by month line graph, saying that December 2015 is 439 (439 being the countd([Customer Name]) filtered on the last 3 months)

December (count of distinct customers based on the 3 months before December)

November:

then build something very simple like that, but with values coming from above (December being 439, November being 445...)

Tried several window sum or date diff but there is something I don't get.

Ben

• ###### 1. Re: Distinct count based on x previous periods

Hi Ben,

Have you tried moving calculation?

Thanks,

Yemeng

• ###### 2. Re: Distinct count based on x previous periods

Hi Ben,

Here you go:

Step 1: create a calculated field:

Step 2: Moving calculation

Please see the attached for more details.

Thanks,

Yemeng

• ###### 3. Re: Distinct count based on x previous periods

Hello,

Thanks for your answer. Looks like I cannot open the twb though.

First thing, I see your logic but 2 issues here

1. To have something by month, and not by day, I need to change the LOD to be month fixed, which is easy:

{fixed datetrunc('month',[Order Date]): countd([Customer Name])}..

2.What bothers me with moving sum is that is sums the count of December + November + October = 553. As shown in the post, I expect to find 439 (which means a customer sold in December,  November and October should appear as one customer, not 3.)

But indeed, the answer is probably some LOD.

If you have some other ideas, I would appreciate it very much.

thanks,

Ben

• ###### 4. Re: Distinct count based on x previous periods

Any idea on the topic? really need your help

thanks you!