Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
jlakehivewyre
Contributor III
Contributor III

Use Color Expression to highlight difference greater than 20% of comparison

Hello,

I have a Pivot table that is comparing an accounts revenue between two dates; looks like this:

AccountDate 1Date 2
Advertiser A205.40199.65
Advertiser B543.99201.01
Advertiser C199.20465.77

Currently, I'm using a background color expression to highlight whether the number value in "date 2" is higher or lower than the data in the corresponding "Date 1" column.

This is my expression: If(Sum([Revenue (USD)])>before(Sum([Revenue (USD)])), 'green', If(Sum([Revenue (USD)])<before(Sum([Revenue (USD)])), 'red'))

This is working as expected.  Applied to the example above it yields:

AccountDate 1Date 2
Advertiser A205.40199.65
Advertiser B543.99201.01
Advertiser C199.20465.77

The problem I'm having, is I really only want it to highlight if it's a significant enough difference to warrant it.  Sy 20% more or less.  So in the case of Advertiser A; I'm not really concerned, that it spent $6 less; it's within an acceptable range to not be of concern.  But in both other cases, the differences between Dates 1 and 2 are significant enough to highlight.

My ultimate data set has 75 or so advertisers it's comparing day over day, and it turns into a bit of noise when trying to pick out those that may need the most attention.

Is there a way to modify the expression to not be a little more flexible?

Thanks!

1 Solution

Accepted Solutions
sunny_talwar

May be this

If((Sum([Revenue (USD)])/Before(Sum([Revenue (USD)])) - 1) > 0.2, 'Green',

If((Sum([Revenue (USD)])/Before(Sum([Revenue (USD)])) - 1) < -0.2, 'Red'))

View solution in original post

6 Replies
YoussefBelloum
Champion
Champion

Hi,

try something like this:


=If( (Sum([Revenue (USD)]) / before(Sum([Revenue (USD)])))> 0.2 , 'green', ETC

sunny_talwar

May be this

If((Sum([Revenue (USD)])/Before(Sum([Revenue (USD)])) - 1) > 0.2, 'Green',

If((Sum([Revenue (USD)])/Before(Sum([Revenue (USD)])) - 1) < -0.2, 'Red'))

jlakehivewyre
Contributor III
Contributor III
Author

Huzzah!  It worked.  This is perfect, and really helps me out.  Thank you for the quick response as well!

jlakehivewyre
Contributor III
Contributor III
Author

Thank you!  This works, and I'm able to tweak that 20% to be whatever I need.  Very helpful

sunny_talwar

Just out of curiosity, the expression worked without subtracting 1? I would have thought that to get 0.20, you would need to subtract -1... but I might have missed something.

jlakehivewyre
Contributor III
Contributor III
Author

I'm sorry - you were right, I did end up using yours (with the -1); and therefore updated the "Correct Answer".