Solved: Can I change color gradient with color expression?

[Keitaru]

Hi Need Help,

With expression by color am I able to change the color of certain input based on the status of  another datafield.

My Data below looks something like this

Expiry Date	A	B	C	D	Status
02/04/2019	1				Complete
01/01/2016		1			Expired
25/12/2018			1		In-Progress
02/01/2019				1	In-Progress

For the status column I want to have the 'In-progress' changed to different shades of amber based on the status of columns A,B,C,D.

The code in my load script for the status is as mentioned below

Load *,

if(interval([ExpiryDate]-today(),'d') < 0,'Contract Expired',
   if(interval([ExpiryDate]-today(),'d') >= 0 and interval([ExpiryDate]-today(),'d') < 30, 'In-Progress',
      if(interval([ExpiryDate]-today(),'d')>30,'Complete')))as Status

From <table>

Color Expression

if(Status='Contract Expired',rgb(255,0,0),
   if(Status='Complete',rgb(0,255,0),
      if(Status='In-Progress',
         if(len(A) > 0,rgb(255,230,0),
         if(len(B) > 0,rgb(255,220,0),
         if(len(C) > 0,rgb(255,200,0),
         if(len(D) > 0,rgb(255,191,0))))))))

Some how In-progress is color is not appearing.

Any advice?

[sunny_talwar]

The expression in itself looks fine, does it only happen when you make selections? Also, are you able to share a sample where we can see the issue to help you better?

[Keitaru]

Got the earlier one to work however due to new improvements from my management 

Got the gradient to work but not the RAG

if([Contract Status]='Contract Renewed',rgb(0,255,0),
if([Contract Status]='Contract Expired',
    if([Process Status]='Not Started',RGB(191,0,0),
    if([Process Status]='Pending PR',RGB(201,0,0),
    if([Process Status]='Pending PO',RGB(211,0,0),
    if([Process Status]='Pending GR',RGB(221,0,0),
if([Contract Status]='Contract Expiring in 90 Days',
    if([Process Status]='Not Started',RGB(255,191,0),
    if([Process Status]='Pending PR',RGB(255,201,0),
    if([Process Status]='Pending PO',RGB(255,211,0),
    if([Process Status]='Pending GR',RGB(255,221,0),
if([Contract Status]='Contract Not Expired',
    if([Process Status]='Not Started',RGB(0,191,0),
    if([Process Status]='Pending PR',RGB(0,201,0),
    if([Process Status]='Pending PO',RGB(0,211,0),
    if([Process Status]='Pending GR',RGB(0,221,0)))))))))))))))))

For a weird reason, I'm only able to have different shades of red to appear but not the Amber and Green shades.

[Keitaru]

Looks like i got it to Display RAG Colours when i flipped the logic around but not the gradient colors 😕 i'm so lost.

if([Process Status]='Not Started',
    if([Contract Status]='Contract Expired',RGB(191,0,0),
    if([Contract Status]='Contract Expiring in 90 Days',RGB(255,191,0),
    if([Contract Status]='Contract Not Expired',RGB(0,191,0),
    if([Contract Status]='Contract Renewed',rgb(0,255,0),
if([Process Status]='Pending PR',
    if([Contract Status]='Contract Expired',RGB(201,0,0),
    if([Contract Status]='Contract Expiring in 90 Days',RGB(255,201,0),
    if([Contract Status]='Contract Not Expired',RGB(0,201,0),
    if([Contract Status]='Contract Renewed',rgb(0,255,0),
if([Process Status]='Pending PO',
    if([Contract Status]='Contract Expired',RGB(211,0,0),
    if([Contract Status]='Contract Expiring in 90 Days',RGB(255,211,0),
    if([Contract Status]='Contract Not Expired',RGB(0,211,0),
    if([Contract Status]='Contract Renewed',rgb(0,255,0),
if([Process Status]='Pending GR',
    if([Contract Status]='Contract Expired',RGB(221,0,0),
    if([Contract Status]='Contract Expiring in 90 Days',RGB(255,221,0),
    if([Contract Status]='Contract Not Expired',RGB(0,221,0),
    if([Contract Status]='Contract Renewed',rgb(0,255,0)))))))))))))))))))))

[sunny_talwar]

Would you be able to share a sample where might be able to check this out?

[Keitaru]

Anyway I manage to get this working after a few trial and errors 

if([Process Status]='Not Started' and [Contract Status]='Contract Expired',RGB(191,0,0),
if([Process Status]='Not Started' and [Contract Status]='Contract Expiring in 90 Days',RGB(255,191,0),
if([Process Status]='Not Started' and [Contract Status]='Contract Not Expired',RGB(0,191,0),
if([Process Status]='Not Started' and [Contract Status]='Contract Renewed',rgb(0,255,0)

Apparently using If else with And statement did the trick