topic How to Get the count for change in column value in QlikView

How to Get the count for change in column value

Tue, 25 Dec 2012 09:32:29 GMT

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

Re: How to Get the count for change in column value

er_mohit — Tue, 25 Dec 2012 10:54:06 GMT

use this expression:

count(distinct P_ID)

for colour use

count(colour)

hope it helps you

Re: How to Get the count for change in column value

Tue, 25 Dec 2012 11:01:55 GMT

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.

Re: How to Get the count for change in column value

Tue, 25 Dec 2012 11:11:31 GMT

Thanks for update

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

Re: How to Get the count for change in column value

Tue, 25 Dec 2012 11:12:41 GMT

It is exactly my solution do.

Re: How to Get the count for change in column value

Tue, 25 Dec 2012 11:15:42 GMT

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.

Re: How to Get the count for change in column value

Tue, 25 Dec 2012 11:27:19 GMT

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

Re: How to Get the count for change in column value

Tue, 25 Dec 2012 11:35:02 GMT

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?

Re: How to Get the count for change in column value

Tue, 25 Dec 2012 11:50:12 GMT

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