Count up

[#1]

Hello!

I need to create a run chart with phases. Each phase is defined by a change in a certain column.

COL1                                        COL2     COL3

T=1, R02, P=2, T2=3, R03=5     0               5

T=1, R02, P=2, T2=3, R03=5     0               5

T=1, R02, P=2, T2=3, R03=5     0               5

T=1, R02, P=2, T2=3, R03=5     0               5

T=1, R02, P=2, T2=3, R03=5     0               5

T=1, R02, P=2, T2=2, R03=5     1               5

T=1, R02, P=2, T2=2, R03=5     1               5

T=1, R02, P=2, T2=2, R03=5     1               5

T=1, R02, P=2, T2=2, R03=5     1               5

T=5, R02, P=2, T2=2, R03=4     2               4

T=5, R02, P=2, T2=2, R03=4     2               4

T=5, R02, P=2, T2=2, R03=4     2               4

T=5, R02, P=2, T2=2, R03=4     2               4

T=5, R02, P=2, T2=2, R03=4     2               4

The text in the column is longer than this. In the run chart I don't want the full text, only the value that has changed (as in COL3). Or if that isn't possible perhaps just a counter (as COL2). The thing is that more than one value could have changed in COL1.

The counter is shown in the second column. My question is
- Can I make the chart with the changed values (as in COL3 and other columns like it) as phase labels in a run chart?

- If not, How do I make the counter shown in COL2, which in Excel is easily done by referencing to its previous value (Col2(n)=Col2(n-1)+1). I have tried referencing the same column with a subscript, but it doesn't seem to work.

[#2]

The LAG function allows you to look at previous row values.

[#3 · Accepted solution]

I you wanted to make a counter that increments every time a text (column 1) row changes- use the lag function, first to compare a row with the prior, then to increment the prior count if different, otherwise copy the count..  Initialize the counter at zero for row 1.

Formula for a count column

IF(

     Row()==1,0

     :column 1 != Lag(:column 1, 1), Lag (:column 2, 1) +1,

     Lag(:column 2, 1))

If you wanted to pull the individual components out of the text column 1, character functions such as munger, left, right, and word may be helpful.

[#4]

Thanks. I have to use LAG if I have text in column 1, and DIF if I have numbers.

This also works, is a bit more like Excel, and is independent on what type of data I have in column 1:

If(

Row() == 1, 0,

:Column 1[Row() - 1] != :Column 1[Row()], :Column 2[Row() - 1] + 1,

:Column 2[Row() - 1]

)

Now all I need is how to control the phase labels...?