4 Replies Latest reply on Jan 6, 2014 8:24 AM by Jonathan Drummey

# Count first occurrences of a dimension over time

Hi,

I'm trying to count the number of new occurrences of a dimension over time.  Let's say I have data as follows:

 Color Week Blue 11/25/2013 Red 11/25/2013 Red 11/25/2013 Blue 12/2/2013 Red 12/2/2013 Green 12/2/2013

I would like a calculation to determine the first week a new color comes into the data set and I would like to create a simple graph that shows Week and count of first occurrence.  In the example above, I would want the week of 11/25 to show a value of two and the week of 12/2 to show a value of one.

The attached has my attempt at doing this.  My formula called 'zcUniqueColors_020' is displayed in the view with Week and Color.  I would like to show just the week and sum up the 'zcUniqueColors_020' 1's and 0's to get my unique new occurrences by week.  But I've been unable to do this.

I can solve this problem easily by using SQL in the source database, however I'm trying to expand upon my Tableau & DAX skills.  In researching this, I came accross the thread:  How to count distinct users on a running period

which I believe is related to my issue.  If it is similar to my issue, then it sounds like this would be best handled via SQL as opposed to Tableau - but I would love for someone to prove me wrong!

Any insight is appreciated.

Stu

• ###### 1. Re: Count first occurrences of a dimension over time

I am a beginner when it comes to table calculations, but at least the attached seemingly works.

.

```IF WINDOW_MIN(MIN([Week])) = ATTR(Week) THEN 1 ELSE 0 END

```

.

• ###### 2. Re: Count first occurrences of a dimension over time

I've attached two workbooks, one showing a common technique for 8.0, the other is an 8.1 workbook showing how we can use RANK_UNIQUE. There's a third technique that I use more often as it's faster for large data sets, and that's to have the Tableau data source be a query or view that has a subquery that returns the first occurrence as an additional dimension.

Both workbooks uses a similar process:

- Have a table calculation to identify the earliest week. This has a compute using of the Week so that it is computed for each Week and partitioned on the Color. In the 8.0 workbook, the calc uses PREVIOUS_VALUE(WINDOW_MIN(DATETRUNC('week',MIN([Week])))). The WINDOW_MIN() part gets the earliest week for the Color, the PREVIOUS_VALUE() is an optimization so the calc is only computed once for each Color. In the 8.1 workbook, the formula RANK_UNIQUE(MIN([Week]),'asc') is used to identify the first non-null Row in each partition.

- Have a second table calculation that does a IF FIRST()==0 THEN ZN(WINDOW_SUM(IF [first week calc shows a value] THEN 1 END)) END. This calc has a nested compute using, so the inner calc's compute using is retained while this calc has a compute using of the Color, so it partitions on each Week. The WINDOW_SUM() sums up all the first weeks, the IF FIRST()==0 is used to return only a single value for each Week.

Jonathan

• ###### 3. Re: Count first occurrences of a dimension over time

Hi Jonathan,

I am struggling to create start and stop dates as dimentions based on a date field in my source data. I suspect that the third technique that you refer to in your reply above may be the solution to achieve this.

I am not an SQL-wiz, so could I trouble you for an example of how such query/subquery looks like?

I am working with source data in MySQL.

Appreciate to hear

Kind regards,

Geir

• ###### 4. Re: Count first occurrences of a dimension over time

Hi Geir,

See this thread for details, the queries are written for JET SQL but the syntax should be pretty close. http://community.tableau.com/thread/137236.

Jonathan