topic Compare values in cells in QlikView

Compare values in cells

Thu, 18 Nov 2010 19:42:50 GMT

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..

Compare values in cells

john_duffy — Thu, 18 Nov 2010 20:45:11 GMT

Hello.

You can expand the options on each dimension or expression and set the background or text colour. This can be done by simply assigning a colour (eg. red()) or by entering an expression (eg. if (amount > 10,red(),green()).

Let me know if this helps.

John.

Compare values in cells

Thu, 18 Nov 2010 21:28:53 GMT

Thanks for your reply. Its not based on condition. Its based on value of prior record value

Compare values in cells

Thu, 18 Nov 2010 21:30:55 GMT

I guess I need to use Last function but I am not sure how to use it on a field

Compare values in cells

john_duffy — Thu, 18 Nov 2010 21:41:00 GMT

How about using the above function? Something like if (above(sum(fieldA)) > sum(fieldA),green(),red()).

Compare values in cells

Thu, 18 Nov 2010 21:49:12 GMT

Above may work but its giving error..syntax issue may be

Compare values in cells

hector — Thu, 18 Nov 2010 21:51:37 GMT

hi,

maybe with the first line, above() is null, you need to change this

rgds

Compare values in cells

Thu, 18 Nov 2010 21:54:42 GMT

I am not sure what should I do?

Its like compare value with above and change background to green if more than above else red

Compare values in cells

hector — Thu, 18 Nov 2010 22:03:09 GMT

Hi,

something like this?

rgds

Compare values in cells

Thu, 18 Nov 2010 22:07:45 GMT

wow hector..it looks like i need this..can you please send that expression,

Compare values in cells

hector — Thu, 18 Nov 2010 22:12:38 GMT

Hi,

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

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

Rgds

Compare values in cells

Thu, 18 Nov 2010 22:25:55 GMT

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

Compare values in cells

Fri, 19 Nov 2010 15:41:05 GMT

Can someone reply to this?

Thanks

Compare values in cells

john_duffy — Fri, 19 Nov 2010 17:52:32 GMT

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.

Compare values in cells

Fri, 19 Nov 2010 19:07:23 GMT

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

Compare values in cells

hector — Fri, 19 Nov 2010 19:25:46 GMT

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

Compare values in cells

Fri, 19 Nov 2010 20:02:54 GMT

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.

Compare values in cells

Mon, 22 Nov 2010 16:40:47 GMT

Hector or john, can you please reply for this?

thanks

Compare values in cells

john_duffy — Mon, 22 Nov 2010 17:38:42 GMT

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.

Compare values in cells

Tue, 30 Nov 2010 16:28:54 GMT

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