Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to Get the count for change in column value

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

8 Replies
er_mohit
Master II
Master II

use this expression:

count(distinct P_ID)

for colour use

count(colour)

hope it helps you

Not applicable
Author


Hello,

I used theese data, similar to yours:

DateP_IDChange
jan 1Green
jan1Green
jan1RED
jan1Green
jan1Green
jan1RED
jan1RED
jan1RED
feb2Green
feb2Green
feb2RED
feb2RED
feb2Green

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.

Not applicable
Author

Thanks for update

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

Not applicable
Author

It is exactly my solution do.

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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?

Not applicable
Author

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