topic Counting cells in QlikView

Counting cells

Wed, 08 Dec 2010 20:36:41 GMT

Here is the sample inline load.

Sales:
LOAD * INLINE [
Reg, Plname, Year,2G%, 3G%
AB, Plt1,2008,12.3,14.5
AB, Plt1,2009,12.6,14.3
AB, Plt1,2010,12.4,14.9
AB, Plt2,2008,23.3,32.5
AB, Plt2,2009,22.9,34.5
AB, Plt2,2010,22.5,54.5
BC, Plt3,2008,13.3,14.5
BC, Plt3,2009,17.6,14.3
BC, Plt3,2010,19.4,14.9
BC, Plt4,2008,73.3,12.5
BC, Plt4,2009,42.9,43.5
BC, Plt4,2010,45.5,45.5
CD, Plt5,2008,62.3,24.5
CD, Plt5,2009,15.6,64.3
CD, Plt5,2010,62.4,34.9
CD, Plt6,2008,33.3,52.5
CD, Plt6,2009,12.9,74.5
CD, Plt6,2010,32.5,54.5
];

I have expressions for background colors for last 2 which are expressions fields. Now I want to count the number of reds and greens based on each region/plant/year.Based on that I have some expressions to calculate some values and show them for each region for that year. Please let me know how to count them, may be store in a variable and use in another graph or table.

And also, say for example a value for 2008 is null then next value for 2009 is turning green. How can I have it white till it has a prior value for comparision? Right now Im using this for b/g color.

if(rowno()=1,null(),if(above([2G%])<[2G%],lightred(),lightgreen()))

Please let me know if you have any questions.

Thanks

Counting cells

Wed, 08 Dec 2010 23:25:50 GMT

Can someone reply to this please?

Thanks

Counting cells

Thu, 09 Dec 2010 17:31:40 GMT

Can anyone help on this pleaseee?

Thanks

Counting cells

rwunderlich — Thu, 09 Dec 2010 21:45:03 GMT

Something like:

if(
rowno()=1,null()
,if(len(above([2G%]))=0, white()
,if(above([2G%])<[2G%],lightred(),lightgreen()
)))

-Rob

Counting cells

Fri, 10 Dec 2010 21:39:12 GMT

Hai rob, Is there a way I can count the number of greens and number of reds for each metric?

Counting cells

Fri, 10 Dec 2010 22:27:17 GMT

Not very clear

Can you explain the expressions to set the color. How do you define a red ? a green ?

Counting cells

Fri, 10 Dec 2010 22:51:00 GMT

Let's define the green as the number of progression between 2 years

Create in the script a new variable if( previous ( Metric ) < Metric , 1 , 0) as Green

Then in a chart by region, plant , just use sum(Green)

For the red, add a new expression ; count( Metric) - sum(Green) - 1

Counting cells

Fri, 10 Dec 2010 22:56:13 GMT

To take account the break of plant :

if( previous(Plname)<> Plname , Null() , if( previous (Metric ) < Metric , 1 , 0)) as Green

Counting cells

Mon, 13 Dec 2010 18:16:11 GMT

Hai Jean,

thanks for the reply. this previous function is giving me error in expression..!