Background Color Expression

[khanashique]

Hi Experts,

I am trying to put background color expression as shown below,

scenario is if Balance is greater than Budget then RED else Green and if TDC element is "Sales" and Balance is greater than Budget then Green else RED. (TDC Element, Balance, Budget are column names)

if((Month = '1' and Balance > BudgetAmount and Period = '1', rgb(255,0,0), rgb(0,192,0)) OR

if(Month = '1' and TDC_Element = 'Sales' and Balance > BudgetAmount and Period = '1',rgb(0,192,0), rgb(255,0,0))

Have tried to use the above expression but hard luck it does not work.

Thanks,

MAK.

[Thiago_Justen_]

Could you please share a mock up?

[jonathandienst]

Try this:

if(Month = '1' and TDC_Element = 'Sales' and Balance > BudgetAmount and Period = '1', rgb(0,255,0),

if(Month = '1' and TDC_Element = 'Sales' and Balance <= BudgetAmount and Period = '1', rgb(0,192,0)

if(Month = '1' and TDC_Element <> 'Sales' and Balance > BudgetAmount and Period = '1', rgb(192,0,0)))

if(Month = '1' and TDC_Element <> 'Sales' and Balance <= BudgetAmount and Period = '1', rgb(255,0,0)))

if(Month = '1' and TDC_Element = 'Sales' and Balance > BudgetAmount and Period = '1', rgb(0,255,0),

if(Month = '1' and TDC_Element = 'Sales' and Balance <= BudgetAmount and Period = '1', rgb(0,192,0),

if(Month = '1' and TDC_Element <> 'Sales' and Balance > BudgetAmount and Period = '1', rgb(10,192,0),

if(Month = '1' and TDC_Element <> 'Sales' and Balance <= BudgetAmount and Period = '1', rgb(255,0,0)))))

[jonathandienst]

Or

if(Month = '1' and Period = '1',

     if(TDC_Element = 'Sales',

          if(Balance > BudgetAmount, rgb(255,0,0), rgb(0,192,0))),

     if(TDC_Element <> 'Sales',

          if(Balance > BudgetAmount, rgb(0,192,0), rgb(255,0,0)))

)

[khanashique]

Hi Thiago,

Please find below table, Actuals are Balance column for each month same with Budget with each month:

TDC_Element	Budget	Actual	Budget	Actual	Budget	Actual
Sales	-109,147	-6,947	-110,893	-18,862	-112,507	-17,800
Materials Cost	32,090	5,361	32,165	11,631	31,310	10,528
Operating Expenses	9,369	12,426	9,560	8,185	9,751	15,260
Utilities Expenses	1,391	-2,895	1,419	2,448	1,447	3,788
Outbound freight	4,653	9,695	4,748	4,444	4,843	6,040
Other Expenses	2,152	1,301	2,196	1,096	2,240	1,808

Thanks,

MAK.

[khanashique]

Hi Jonathan,

Below expression provided by you, seems working but gives result for one condition only

if(Month = '1' and Period = '1',

     if(TDC_Element = 'Sales',

          if(Balance > BudgetAmount, rgb(255,0,0), rgb(0,192,0))),

     if(TDC_Element <> 'Sales',

          if(Balance > BudgetAmount, rgb(0,192,0), rgb(255,0,0)))

)

Thanks,

MAK.

[khanashique]

Hi Jonathan,

Below expression does not return any result...

if(Month = '1' and TDC_Element = 'Sales' and Balance > BudgetAmount and Period = '1', rgb(0,255,0),

if(Month = '1' and TDC_Element = 'Sales' and Balance <= BudgetAmount and Period = '1', rgb(0,192,0),

if(Month = '1' and TDC_Element <> 'Sales' and Balance > BudgetAmount and Period = '1', rgb(10,192,0),

if(Month = '1' and TDC_Element <> 'Sales' and Balance <= BudgetAmount and Period = '1', rgb(255,0,0)))))

Thanks,

MAK.

[khanashique]

Hi Jonathan,

Please see the snapshot below, Sales have not been applied any colour expression:

Thanks,

MAK.

[khanashique]

Hi Thiago / Jonathan,

A mistake i was doing, i did not do the summing of the balance and budget, after applying the following expression including sum expression, i got 'Sales' green but the other element is not red

if(TDC_Element = 'Sales' and Sum({<Year = {'2018'}, Month = {'1'}>}Balance) > Sum({<Year = {'2018'}, Period = {'1'}>}BudgetAmount) , 'Green',

if(TDC_Element <> 'Sales' and Sum({<Year = {'2018'}, Month = {'1'}>}Balance) > Sum({<Year = {'2018'}, Period = {'1'}>}BudgetAmount) , 'Red'))

when i apply following expression if i got other elements green but sales is not red

if(Year = '2018' and Month = '2' and Period = '2',

     if(TDC_Element = 'Sales',

          if(Sum(Balance) > Sum(BudgetAmount), rgb(255,0,0), rgb(0,192,0))),

      if(TDC_Element <> 'Sales',

           if(Sum(Balance) > Sum(BudgetAmount),  rgb(255,0,0), rgb(0,192,0)))

Just have a look on the above mentioned expressions.

Thanks and regards,

Ashik.

[khanashique · Accepted solution]

Hi All,

Please see the below expression, this worked perfectly fine as needed...Thanks a lot...

if(TDC_Element = 'Sales' and Sum({<Year = {'2018'}, Month = {'5'}>}Balance) > Sum({<Year = {'2018'},

Period = {'5'}>}BudgetAmount) , 'Green',

if(TDC_Element = 'Sales' and Sum({<Year = {'2018'}, Month = {'5'}>}Balance) < Sum({<Year = {'2018'},

Period = {'5'}>}BudgetAmount) , 'Red',

if(TDC_Element <> 'Sales' and Sum({<Year = {'2018'}, Month = {'5'}>}Balance) > Sum({<Year = {'2018'},

Period = {'5'}>}BudgetAmount) , 'Red',

if(TDC_Element <> 'Sales' and Sum({<Year = {'2018'}, Month = {'5'}>}Balance) < Sum({<Year = {'2018'},

Period = {'5'}>}BudgetAmount) , 'Green'))))

Regards,

MAK.