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
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.
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