How to Get the count for change in column value

[#1]

hi,

i am looking for script which give me the count for chnage in column value.

My source table look like

Date                P_ID               Status

1 jan 2012          1                    Green

2 jan 2012          1                    Green

3 jan 2012          1                    RED

4 jan 2012          1                    Green

5 jan 2012          1                    Green

6 jan 2012          1                    RED

7 jan 2012          1                    RED

8 jan 2012          1                    RED

.........

1 Feb 2012          2                    Green

2 Feb 2012          2                    Green

3 Feb 2012          2                    RED

4 Feb 2012          2                    RED

5 Feb 2012          2                    Green

6 Feb 2012          2                    Green

........

and output which i need look lke

Month          P_ID          count of Change to Red

Jan 2012         1               2

Feb 2012         2              1

thanks

[er_mohit]

use this expression:

count(distinct P_ID)

for colour use

count(colour)

hope it helps you

[#3]

Hello,

I used theese data, similar to yours:

Date	P_ID	Change
jan	1	Green
jan	1	Green
jan	1	RED
jan	1	Green
jan	1	Green
jan	1	RED
jan	1	RED
jan	1	RED
feb	2	Green
feb	2	Green
feb	2	RED
feb	2	RED
feb	2	Green

I load it in QV as is, and then created Straight table with Date & P_ID as Dimentions, and this simple expression with set modifier:

TextCount(Change)-count({<Change={Green}>} Change)

In first part I am counting all the lines in Change row, in second only the values that are green, so I minus them from whole range and here it is included as attachment. It is simple but not only the way to do something like that, everything depends on current tass within whole QVW model.

If you will use further more complex indicators and will add more colors you should change the logic of it.

If you have any questions, ask.

[#4]

Thanks for update

I am looking for how many time  product status change to red in each  month.

[#5]

It is exactly my solution do.

[#6]

You can also write srict set modifier without minusing:

The Expression

count({<Change={RED}>} Change) and it will count only red status, so you will see the same picture.

[#7]

thanks for your reply

Your sol give count of total no of red. My requirment is only count those red where  previous record is green.

mean  how many time prod status is change from green to red

in above data table for jan we shoud get 2 for prod id 1. as it is only change two time in month of jan from green to red.

thanks

[#8]

Yes, I understand it.

But as I see in your data, in Jan product ID 1 is changed to RED four times, am I correct?

[#9]

but i am looking for only those where it is change from green to red