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

    Count first occurrences of a dimension over time

    Stuart Dunlap

      Hi,

       

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

       

      ColorWeek
      Blue11/25/2013
      Red11/25/2013
      Red11/25/2013
      Blue12/2/2013
      Red12/2/2013
      Green12/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