Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jlakehivewyre
New 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

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

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'))

6 Replies
YoussefBelloum
Esteemed Contributor

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

Hi,

try something like this:


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

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

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
New Contributor III

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

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

jlakehivewyre
New Contributor III

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

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

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

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
New Contributor III

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

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

Community Browser