topic Re: Comparing two columns and show BG color in QlikView

Comparing two columns and show BG color

Thu, 23 Jul 2015 03:02:24 GMT

Hi all,

Thanks for all your time for viewing this post.

I have searched through the forum but could not find a solution to my problem.

What I am trying to do is to compare two columns in a pivot table, and show red in bg if the value in the second column is greater than the first, and green if the second less than the first. I've tried to use both the column expression as well as the function column() to compare the two values. However, none of them returned the correct visual cues.

The bg color changed correctly for all the correct comparison. However, it also changed sometimes when the values are the same.

The expression I used:

if(avg({$<Revision={'70'}>}CPU)>avg({$<Revision={'60'}>}CPU), lightred(),

if(avg({$<Revision={'70'}>}CPU)<avg({$<Revision={'60'}>}CPU),green()))

and

if(column(2)>column(1), lightred(),

if(column(2)<column(1),green()))

in the definition of bg color.

Anyone has any idea what's wrong with this?

Thanks!

Re: Comparing two columns and show BG color

MayilVahanan — Thu, 23 Jul 2015 09:42:46 GMT

Try like this

In chart properites -> number tab, changed the fixed decimal point from 2 to 7(for ex), and you can able to find the difference between 2 columns. There might be a small difference.

In that case, you can use Round(), Ceil() or Floor() function based on your requirement.

hope it helps

Re: Comparing two columns and show BG color

a_mullick — Thu, 23 Jul 2015 09:45:40 GMT

Hi,

Are you rounding expressions to 2 dp? If you are then you may be obscuring the 'true' values of column 1 and oclumn 2 e.g. column 1 may be 16.68 and column 2 may be 16.66 - both round to 16.70, but column 2 is really less than column 1.

Azam

Re: Comparing two columns and show BG color

Thu, 23 Jul 2015 10:16:28 GMT

‌Hi

thanks for your reply!

I Suspected what you said as well. However, the actual value for 16.80 is 16.8. It appears to be 16.80 because I set it to be fixed to 2 decimal point. So it is not because of rounding of numbers.

Re: Comparing two columns and show BG color

Thu, 23 Jul 2015 10:17:06 GMT

‌Hi

thanks for your reply!

Please refer to my post above. Thanks!

Re: Comparing two columns and show BG color

avinashelite — Thu, 23 Jul 2015 10:23:05 GMT

Hi,

can you please share your app??

Re: Comparing two columns and show BG color

MayilVahanan — Thu, 23 Jul 2015 10:23:57 GMT

Please provide sample data

Re: Comparing two columns and show BG color

vardhancse — Thu, 23 Jul 2015 10:55:32 GMT

Hi Try,

=Expression_1>=After(Expression_2) or Expression_1>=Before(Expression_2),'▲','▼'))

Re: Comparing two columns and show BG color

Fri, 24 Jul 2015 03:16:54 GMT

Hi all,

Thanks for all your help.

I have solved the problem. In my previous version, the column 60 and 70 are separate expressions I created using set Analysis. They are actually under one column called Revision in the table. Now I added in Revision as a dimension and move it to the row, and the color appears to be correct now. I am not sure how it is related.