7 Replies Latest reply on Dec 12, 2016 5:41 AM by Shinichiro Murakami

# How to count distinct user on a running period comparing to the previous periods

Hi Everyone,

Here is my requirements. I have a daily data.

For requirement #1 I have to check the unique counts of customer id daily that were present on the last 7 days, Let say if there are 2 registrations today but they are all present on the last 7 days, then NO NEW registration for today. If they were NOT, then they were count as NEW registration for today.

For requirement #2, I have to sum all the new registration daily. let say if today is Nov 3 and  new registration on Nov 1 = 1, Nov 2 =3, Nov 3 = 1 , then total new registration for today should be = 5.

I really appreciate your help on this.

• ###### 1. Re: How to count distinct user on a running period comparing to the previous periods

Hi Alfie,

It is hard to do this without looking at actual data, but here is how I will go about achieving this.

1. Create a set of customers based on last 7 days. Check if the today's registration is present in the set or not. If it is not there, then it will be new registration for today

2. You can simply do running sum of registrations across the time period

Hope this helps.

Thanks,

Nachiket

• ###### 2. Re: How to count distinct user on a running period comparing to the previous periods

Hi Nachiket,

Attached is my working file.

Hope you can help me on this.

Thanks,

Alfie

• ###### 3. Re: How to count distinct user on a running period comparing to the previous periods

Alfie

Table calculation is always tricky.

I hope this helps but please verify.

[Count (last 7 days)]

zn((min(1,window_sum(countd([Id]),-6,-1))))

[Count New ]

if [Count (last 7 days)]=0 and countd([Id])>0 then 1 else 0 end

[Cum Count New]

window_sum([Count New ],first(),0)

[Sum Cum Count New]

window_sum([Cum Count New])

Then because using  [Sum Cum Count New] as label brings multiple value, I added label by reference line.

Thank,

Shin

• ###### 4. Re: How to count distinct user on a running period comparing to the previous periods

Hi Shin,

Appreciate your help on this and thank you for sharing your insights.

However, I need to have a comparison of IDs if it is existing on the last 7 days.

Like on my given example, for Nov 2, ID A is still present on the last 7 days. Hence my distinct count of new registration for Nov 2 should be = 0.Then for Nov 3 it should be = 1, Nov 4 = 0 and Nov 5 = 1...

• ###### 5. Re: How to count distinct user on a running period comparing to the previous periods

Hi Alfie,

I'm pretty confused.

You data which you used as sample don't have enough data.

If you explain the exapmle based on your attahded workbook data, that's more helpful.

Is any of Sheet 4, 5, 6 shows what you want?

Thanks,

Shin

• ###### 6. Re: How to count distinct user on a running period comparing to the previous periods

Hi Shin,

BTW, your sheet #4 is exactly what I need.

Wow!! I was now able to solve my first requirement. I really appreciate your help on this.

My question now is how can I get the commutative sum of those new registrations?

Please see attached my desired output.

Also, if you have time, I would like to understand your formulas and logic behind it.

I'm just new to tableau and this kind of forum helps me a lot.

Thanks,

Alfie

• ###### 7. Re: How to count distinct user on a running period comparing to the previous periods

I'm very confused.

Cumulative one is exactly I showed at the first reply (sheet 2)??

All the logic is in the formula.

You can take you time to interpret it.

Thanks,

Shin