Conditional Color R,Y,G based on % Change

[blakeB]

Using the below formula to show red or green based off previous month's conversion rate (table reference).

=If(above(sum({<Month={'$(=max(Month))'}>} [Overall Conversion Rate])) > sum({<Month={'$(=max(Month))'}>} [Overall Conversion Rate]), red(), green())

Not sure how to improve this expression to red, yellow, green based off threshold. For example, if previous month conversion was less than 1% change up or down I'd like it to show yellow.

Any insights or help would be great.

Thanks

[vvira1316]

You will need to implement conditional check like below

 https://community.qlik.com/t5/New-to-QlikView/More-than-3-segments-for-Traffic-light-gauge-represent...

=If(Expected=0,'qmem://ImageID/Grey',
If(Actual>=Expected, 'qmem://ImageID/Green',
If(Actual < Expected and (Expected-Actual)/Expected*100 <= 9, 'qmem://ImageID/Yellow',
'qmem://ImageID/Red')))

The following three if conditions criterias can be replaced 

Expected=0, Actual>=Expected, Actual < Expected and (Expected-Actual)/Expected*100 <= 9

with your conditions

and

'qmem://ImageID/xxxx' can be replaced with RGB codes

You will need to check something like following

CurrentMontConversion% - PreviousMonthConversion% > 1 then Red

 (CurrentMontConversion% - PreviousMonthConversion% <= 1) or  

(PreviousMonthConversion% - CurrentMontConversion% <= 1) then Yellow

Not sure what is your green criteria

If needed please share a sample file with mock data to help you further.

[blakeB]

Could i form an if() statement with these conditions?
(current-previous)<0.1,yellow
(current-previous)>0.1,green
(current-previous)<-0.1,red

[vvira1316]

You can definitely try but I would advise it to be changed to following

(current-previous)<=0.1 and (current-previous) >= -0.1,yellow // This would be between up and down 1%
(current-previous)>0.1,green //This would be greater than 1%
(current-previous)<-0.1,red // This would be less than -1%

[blakeB]

Awesome, I will definetely use that. Could you provide an example of how to string this together?

[timpoismans]

If((current-previous)<=0.1 and (current-previous) >= -0.1,Yellow(),
If((current-previous)>0.1,Green(),
If((current-previous)<-0.1,Red())))

This should do the trick. The editor for measures is a big help with brackets as it indicates which brackets belong together, so should use it to check them ^^

[blakeB]

I tried using this expression and it doesn't seem to be working for me. I am referencing the latest value in a table, so current would be January's value. And previous is the above value, which would be December's value. The string I'm plugging in is:

=IF((sum({<Month={'$(=max(Month))'}>} [TotalTransactions]) - above(sum({<Month={'$(=max(Month))'}>} [TotalTransactions]))))<0.1 and (sum({<Month={'$(=max(Month))'}>} [TotalTransactions]) - above(sum({<Month={'$(=max(Month))'}>} [TotalTransactions]))) >= -0.1, Yellow(), if((sum({<Month={'$(=max(Month))'}>} [TotalTransactions]) - above(sum({<Month={'$(=max(Month))'}>} [TotalTransactions])))>0.1,Green(),if((sum({<Month={'$(=max(Month))'}>} [TotalTransactions]) - above(sum({<Month={'$(=max(Month))'}>} [TotalTransactions])))<-0.1,Red()))

I'm not sure what I'm missing here. Any help would be greatly appreciated.

Thanks

[timpoismans]

Like I said, the editor is great for brackets control. Seems like you had an unneeded bracket which forced the If-clause shut. Copy and try the following:

If((sum({<Month={'$(=max(Month))'}>} [TotalTransactions]) - above(sum({<Month={'$(=max(Month))'}>} [TotalTransactions])))<=0.1 
	and (sum({<Month={'$(=max(Month))'}>} [TotalTransactions]) - above(sum({<Month={'$(=max(Month))'}>} [TotalTransactions]))) >= -0.1, Yellow(), 
	If((sum({<Month={'$(=max(Month))'}>} [TotalTransactions]) - above(sum({<Month={'$(=max(Month))'}>} [TotalTransactions])))>0.1,Green(),
		If((sum({<Month={'$(=max(Month))'}>} [TotalTransactions]) - above(sum({<Month={'$(=max(Month))'}>} [TotalTransactions])))<-0.1,Red())))

[blakeB]

When I plug that into the "target color expression" field it doesn't seem to be recognizing the string. If I revert back to a more basic string (just red or green that I mentioned in the beginning of the post) it works. I'm not sure how to resolve it. I'm working with the Climber KPI if you have any experience with that?

[timpoismans]

Can't say I have, but what do you exactly mean by "don't recognize the string"?