
1. Re: Is there any method to group repeated row in a sequence?
Jonathan Drummey Jul 25, 2018 6:56 AM (in response to Jasper Li)2 of 2 people found this helpfulHi,
There isn't one function to do this, instead we can use three calculated fields. The reason why this is a little more complex is that we need to do rowbyrow comparison from the underlying data and the only way we can do that in Tableau Desktop at present is with table calculations.
Here's the workout view:
Note that Sequence Order is used as a dimension while Value is used with the ATTR() aggregation. This simplifies the setup of the three table calculations, they all have a Compute Using on Sequence Order.
The Group Order field's formula looks like this:
IF INDEX() = 1 THEN
1
ELSEIF ATTR([Value]) != LOOKUP(ATTR([Value]), 1) THEN
PREVIOUS_VALUE(0) + 1
ELSE
PREVIOUS_VALUE(0)
END
INDEX() tells us where we are in the partition (i.e. what row we are at). LOOKUP() is Tableau's equivalent of a cell reference in Excel, it lets us look at the prior value of the given field which is ATTR([Value]) in this case. And PREVIOUS_VALUE() is a selfreferencing function in that it returns the prior value of the *current* field, i.e. Group Order. This lets us build out the group order.
The Count field's formula looks like this, it's much the same as Group Order only it's iterating over the values.
IF INDEX() = 1 THEN
1
ELSEIF ATTR([Value]) != LOOKUP(ATTR([Value]), 1) THEN
1
ELSE
PREVIOUS_VALUE(0)+1
END
(This formulation is necessary because we can't partition table calculations on other table calculations, otherwise we'd use a function like COUNTD() or SIZE()).
Finally the Last Value in Sequence Flag has this formula:
LAST() = 0 OR ATTR([Value]) != LOOKUP(ATTR([Value]), 1)
LAST() is a Tableau function that tells us how many rows we are from the last row in the partition, and the last row is 0. That way we can identify the very last Sequence Order value without having to know what it actually is.
As I mentioned all the table calculations have a compute using on the Sequence Order. Having placed them in the Workout view and validated that they work as expected, then I duplicated that worksheet and rearranged pills to get the final view:
I've attached a v10.5 Tableau packaged workbook with the calculations & worksheets. Let me know if you have any questions!
Jonathan

2. Re: Is there any method to group repeated row in a sequence?
Jasper Li Jul 25, 2018 7:34 AM (in response to Jonathan Drummey)Hi Jonathan,
Many Many Thanks for your clear and helpful explanation