KNOWLEDGE BASE

Running Count Groups


Published: 16 Feb 2018
Last Modified Date: 16 Feb 2018

Question

How to group consecutive rows that meet a condition. For example, groups of days based on the number of consecutive days that had a negative profit.

Environment

Tableau Desktop

Answer

The attached example workbook uses the sample data set Superstore to demonstrate the following directions:

1. Create a calculated field with a name like "Running count of consecutive days of neg profit" with a calculation similar to the following:
 
IF SUM( [Profit] ) < 0
THEN PREVIOUS_VALUE(0) + 1
ELSE 0
END

2. Create a calculated field with a name like "Running Count Groups" with a calculation similar to the following:
 
IF [Running count of consecutive days of neg profit] = 0
THEN 0
ELSE MAX(PREVIOUS_VALUE(0), [Running count of consecutive days of neg profit])

3. Drag [Running Count Groups] to the Measure Values card
 
4. Right-click [Running Count Groups] on the Measure Values card and select Edit Table Calculation…
 
5. In the Table Calculation dialog, do the following and close the dialog:
  • For Nested Calculations, select Running count of consecutive days of neg profit
  • For Compute Using, select Table (down)    
  • For Nested Calculations, select Running Count Groups
  • For Compute Using, select Table (down)
  • For Compute Using, select Specific Dimensions
  • For Sort order select Custom, Order Date, Minimum, Descending


 
Did this article resolve the issue?