Subscribe Bookmark RSS Feed

Count up

peder

Community Trekker

Joined:

Jul 27, 2013

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.

1 ACCEPTED SOLUTION

Accepted Solutions
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.


3 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

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

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.


peder

Community Trekker

Joined:

Jul 27, 2013

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...?