Background Color Set Analysis

[#1]

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?

[MayilVahanan]

Hi

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.

[Anonymous]

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

[#4]

Unfortunately neither option worked...

but thank you!

[Anonymous]

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

[jonathandienst]

Is Database a dimension on your chart/table?

[#7]

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!

[JonnyPoole]

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

[#9 · Accepted solution]

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.