3 Replies Latest reply on Dec 14, 2016 2:05 PM by Shinichiro Murakami

# Table Calculation- sum of distinct count over time

Hi Tableau friends -

I'm attempting to get a percent for each period of time (months) as follows:

- numerator: distinct number of sub-categories ordered in 12 out of the 12 following months (inclusive of current)

- denominator: distinct number of sub-categories ordered in the current month

I've got the denominator figured out. Based on the sample workbook, the first few months are:
- Jan 2013: 14

- Feb 2013: 13

- Mar 2013: 16

For the numerator, the values I am trying to obtain are:

- Jan 2013: 12 (there were 12 sub-categories that were ordered in each of the months between Jan 2013 and Dec 2013)

- Feb 2013: 12 (12 sub-categories ordered in each month Feb 2013 - Jan 2014)

- Mar 2013: 13 (13 sub-categories ordered in each month Mar 2013 - Feb 2014)

So the desired % per month would look like:

- Jan 2013: 85.7%

- Feb 2013: 92.3%

- Mar 2013: 81.3%

Any tips on how to produce a rolling distinct conditional count?

Thanks!

Phil

• ###### 1. Re: Table Calculation- sum of distinct count over time

Phillip,

I finally figure out your issue.

You countd iis based on "Date" not month. But duration of period is based on "month", not date.

So, easy way to avoid confusion is change the dimension of month to discrete.

[Numerator 2]

window_max(max({fixed [Order Date]:countd([Sub-Category])}),0,11)

[Percent]

[Numerator 2] / attr(([Unique SubCat Each Month FIXED]))

Thanks,

Shin

• ###### 2. Re: Table Calculation- sum of distinct count over time

Thanks Shin!!

This was helpful for getting my head wrapped around the problem, but unfortunately didn't produce the desired results. I was able to find a thread that more-or-less provided a solution that I needed- although I had to make a few adjustments.

The thread I used is here:

Re: Counting "Active Customers" by month

I'm attaching the solution to this thread and will close it out.

I'll be posting a new thread in a moment with more specifics and an anonymized dataset that will hopefully get more at the specifics I'm interested. Thanks again for looking into this!

Phil

• ###### 3. Re: Table Calculation- sum of distinct count over time

You are welcome, but if you are OK, could you Mark MY answer as correct?

Thanks

Shin