KNOWLEDGE BASE

Grouping consecutive values based on a condition


Published: 16 Feb 2018
Last Modified Date: 28 Jan 2019

Question

How to group consecutive values that meet a condition, or in other words all the values that belong to the same streak.

For example, creating groups of days based on the number of consecutive days that had a negative profit. If there are 3 days in a row with negative profit, those days would all belong to group "3"

Environment

Tableau Desktop

Answer

Using the PREVIOUS_VALUE() function we can count the number of consecutive days in a streak in one calculation that starts with the first value in the data and reads down (e.g. the first day to the last day). Then a second calculation can keep track of the largest non-zero value by reading the values from last value to first (e.g. the last day to the first day).

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


 

Additional Information

Notes on the [Running count of consecutive days of neg profit] calculation:
  • Tableau Desktop will read through each row (aka each day) and if the profit is below zero will return 1 plus whatever what this calculation previously returned.
  • For example, on 3/16/12 the profit is positive, so the calculation returns 0. On 3/17/12 the profit is negative, so the calculation returns 1 + 0 because zero is the value on 3/16. On 3/18/12 the profit is also negative, so the calculation returns 1 + 1, because 1 is the value on 3/17.
  • Unlike LOOKUP(), the function PREVIOUS_VALUE() will return the last value of this calculation
Notes on the [Running Count Groups] calculation:
  • If [Running count of consecutive days of neg profit] is 0, this calculation will return a 0 no matter what
  • Otherwise, this calculation will return whichever is bigger: the current value of [Running count of consecutive days of neg profit] for this row, or the value of this calculation for the previous row. Keep in mind, this calculation will be computed from the last day to the first, aka the bottom of the table to the top.
  • For example,  3/19/12  has a value of 0 for [Running count of consecutive days of neg profit] , so this calculation will return 0.
  • On 3/18/12, [Running count of consecutive days of neg profit]  has a value of 2. The MAX() function will compare 2 (the current value of [Running count of consecutive days of neg profit] ) and 0 (the  value of this calculation on 3/19). Because 2 is larger, this calculation will return 2 on 3/18.
  • On 3/17/12, this calculation will compare 1 and  2 with the MAX() function.
Discuss this article... Feedback Forum
Did this article resolve the issue?