<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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.
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...?
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.
The LAG function allows you to look at previous row values.
