Counting cells

[#1]

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

[#2]

Can someone reply to this please?

Thanks

[#3]

Can anyone help on this pleaseee?

Thanks

[rwunderlich]

Something like:

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

-Rob

[#5]

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

[#6]

Not very clear

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

JJ

[#7]

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

[#8]

To take account the break of plant :

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

[#9]

Hai Jean,

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