Compare values in cells

[#1]

Hai all

I am new to Qlikview. I am working on a table. I populated it with right data, calculated expressions etc.

Can someone suggest me how to do this?

I have a 3 sort fields - Region, country , year and year has 2009 and 2010. so there will be 2 records. I want to make value for 2010 Green if it is more than value for 2009 for that region and country else if it is less I need to make it Red. either change the font color or fill the cell with those colors. And later I need to count the number of greens and reds. How can this be done?

Please let me know of any questions.



Thanks a lot..

[hector]

Hi,

the expression is in the image, but anyway here it is

if(above(Sum(F2))>Sum(F2),lightred(),lightgreen())

Rgds

[#3]

Its working good. Is there anyway, could I count number of greens and reds?

Blank fields are also turning green..? Can I leave them white normally?

And also, base value (1st value for each dimension) should be white when the dimension changes..but its turning green..!!

Thanks

[#4]

Can someone reply to this?

Thanks

[john_duffy]

Hello.

Can you attach a sample application using an inline load. This way I can work on it at my end. Add a description of what you are trying to achieve.

John.

[#6]

Hai John,

I guess you have seen the image attached by Hector. I have similar thing done but I need to count number of greens and reds.

If you see below, cost% and head% are based on dimensions plant and year. For each plant and year 2008, background should be white(i mean should not change) and other 2 values for 2009 and 2010 should be red or green based on condition I gave (which is working as of now). and also, can I count the number of red and greens for each column? for ex right now both cost% and head% have 1 red and 5 greens.

Let me know if you have any questions

Thanks

[hector]

Hi, for the colors issue use rowno()

For the "count of green / red", you need to specify how you want to count them, and in what type of object (table, text box etc), post an example plz

PS. By the way, sometimes the image is too big, so you need to open it in a new window 😄

Rgds

[#8]

Ok. Say for example 2008 has no data, then 2009 should be white and if 2009 also no data then 2010 should be white. should I check for null? what function should I use?

And how come I learn about these functions? Can you please guide me.

[#9]

Hector or john, can you please reply for this?

thanks

[john_duffy]

Hello.

We need you to attach an actual QlikView application so that we can solve the issue at our end. Attach a small application with data created using an inline load in the same format as your actual production application. Keep the record count small ( 5 to 10 rows).

In the applicaiton create the object you are trying to get working (straight table, pivot table, etc.) and create a text box to explain what is working correctly and what you still need.

John.

[#11]

Hey john/hector,

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