topic Re: Re: Background Color Set Analysis in QlikView

Background Color Set Analysis

Mon, 18 Nov 2013 20:50:47 GMT

I created a simple table that has the line counts in two of our environments.

There is a dimension called 'Database' and there are two options its either 'TST' or 'PRD'

I have the line counts for each table. I want to be able to highlight the line if TST does not equal PRD. I am using the following expression in the background color area.

((SUM({<Database={'PRD'}>}BAR_CHARGES))<>(SUM({<Database={'TST'}>}BAR_CHARGES)),YELLOW(),WHITE())

If I use <> then it colors them yellow even if they match

((SUM({<Database={'PRD'}>}BAR_CHARGES))=-(SUM({<Database={'TST'}>}BAR_CHARGES)),YELLOW(),WHITE())

If I use =- then it leaves it white even if it doesn't match

What am I missing?

Re: Background Color Set Analysis

MayilVahanan — Mon, 18 Nov 2013 21:14:06 GMT

May be try like this

=If( SUM({<Database={'PRD'}>}BAR_CHARGES) - SUM({<Database={'TST'}>}BAR_CHARGES) = 0, White(), Yellow())

Note:

Check either difference in decimal once.

Re: Background Color Set Analysis

Anonymous — Mon, 18 Nov 2013 21:24:29 GMT

I think it is where set is not the best way. Try this:

if(SUM(if(Database='PRD', BAR_CHARGES))<>SUM(if(Database='TST', BAR_CHARGES)),YELLOW(),WHITE())

Regards,

Michael

Re: Background Color Set Analysis

Tue, 19 Nov 2013 19:32:03 GMT

Unfortunately neither option worked...

but thank you!

Re: Background Color Set Analysis

Anonymous — Wed, 20 Nov 2013 14:38:52 GMT

Can you upload an example? I suspect something small is missing...

Re: Background Color Set Analysis

jonathandienst — Wed, 20 Nov 2013 14:41:49 GMT

Is Database a dimension on your chart/table?

Re: Background Color Set Analysis

Wed, 20 Nov 2013 19:11:32 GMT

Thanks for taking a look! I am sure you are right, it has to be something simple I am overlooking. I didn't think it was going to be a complicated issue.

Yes, database is a dimension!

Re: Background Color Set Analysis

JonnyPoole — Wed, 20 Nov 2013 20:12:35 GMT

The SET MODIFIER does not overule the same dimensional value that is in the chart column. I modified your example to show this...

To workaround that premise i used the TOTAL arguement in the chart aggregation function to compare over and above the current dimensional value

from:

IF((SUM({<Database={'PRD'}>}BAR_CHARGES))<>(SUM({<Database={'TST'}>}BAR_CHARGES)),YELLOW(),WHITE())

to:

IF((SUM({<Database={'PRD'}>} total BAR_CHARGES))<>(SUM({<Database={'TST'}>} total BAR_CHARGES)),YELLOW(),WHITE())

It works for this isolated use case. Let me know if yours is more complex then your demo example and i'll try to adapt accordingly

Re: Re: Background Color Set Analysis

Wed, 20 Nov 2013 20:39:26 GMT

Hi Katie,

Indeed you need to use the Total qualifier in the background expressions to ignore the dimension.

However I attached a sample file in which i changed your data structure, having a simpler way to compare values in the expressions.

Just in case you see that such a change will be beneficial and/or feasible in your real application.