topic Re: Background Color Expression in App Development

Background Color Expression

khanashique — Sat, 16 Nov 2024 07:43:50 GMT

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.

Re: Background Color Expression

Anonymous — Mon, 03 Sep 2018 09:27:41 GMT

Hi Ashik,

Not clear your logic

kindly try this

if(Month=1,if(Period=1,if(TDC_Element > BudgetAmount and TDC_Element=Sales, RGB(0,255,0),RGB(255,0,0))))

Thanks

Re: Background Color Expression

Thiago_Justen_ — Mon, 03 Sep 2018 10:38:42 GMT

Are you trying to compare TDC_Element (wich is a string value) with BudgetAmount (wich should be a numerical value)?

"TDC_Element = 'Sales' and TDC_Element > BudgetAmount"

This is not possible anyway. If I'm wrong please let me know.

Re: Background Color Expression

khanashique — Mon, 03 Sep 2018 10:54:34 GMT

Hi Thiago,

Yes its a mistake...

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

Here you go, please see now, this is the correct one...i will correct the original discussion too..

Thanks,

MAK.

Re: Background Color Expression

Thiago_Justen_ — Mon, 03 Sep 2018 11:32:29 GMT

So, now, we are on the line. Let's try something like this:

If (Month='1' and Balance > BudgetAmount and Period='1' and Not Match(TDC_Element,'Sales'),Red(),

If(Month='1' and Balance > BudgetAmount and Period='1' and Match(TDC_Element,'Sales'),Green(),Red())

)

Re: Background Color Expression

khanashique — Mon, 03 Sep 2018 12:36:42 GMT

Hi Thiago,

Thanks a lot for your reply, the expression you gave works for one condition only (it shows all red). I am trying 2 conditions

1. Balance > Budget then RED else Green

2. Balance > Budget and TDC Element is Sales then Green else RED

As there are more elements in dimension other than sales, so

If sales (balance) is greater than budget then Green (Why because its sales amount)

If Other elements (balance) is greater than budget then Red (because these are expenses)

Hope this is deep explanation to understand...

Thanks,

MAK.

Re: Background Color Expression

Thiago_Justen_ — Mon, 03 Sep 2018 12:41:07 GMT

May be this:

If (Month='1' and Balance < BudgetAmount and Period='1' OR (Match(TDC_Element,'Sales') and Month='1' and Balance > BudgetAmount and Period='1' ),Green(), Red())

Re: Background Color Expression

khanashique — Mon, 03 Sep 2018 13:06:18 GMT

Hi Yogaraj,

Thanks a lot for your reply, the expression you gave does not work, i have tried to work with your expression by adding some see below:

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

if(Month='2',if(Period='2',if(Balance > BudgetAmount and TDC_Element <> 'Sales', RGB(255,0,0),RGB(0,192,0))))

---------------------------------------------------------------------------------------------------------------------------------------------------

1. Balance > Budget then RED else Green

2. Balance > Budget and TDC Element is Sales then Green else RED

As there are more elements in dimension other than sales, so

If sales (balance) is greater than budget then Green (Why because its sales amount)

If Other elements (balance) is greater than budget then Red (because these are expenses)

Hope this is deep explanation to understand...

Thanks,

MAK.

Re: Background Color Expression

khanashique — Mon, 03 Sep 2018 13:29:57 GMT

Hi Thiago,

Many thanks for spending your precious time on creating expressions, but hard luck it returns expression for one condition only and that is RED.

I have tried below expression by adding the same expression provided by you:

If (Month='1' and Balance > BudgetAmount and Period='1' AND (Match(TDC_Element,'Sales') ,Green(), Red()))

If (Month='1' and Balance > BudgetAmount and Period='1' AND (Not Match(TDC_Element,'Sales'),Green(), Red()))

But it does not work...I believe if you can just have a look at this expression, may be some minor correction is required which i cannot find.

Thanks,

MAK.

Re: Background Color Expression

khanashique — Tue, 04 Sep 2018 06:40:01 GMT

Hi Thiago,

Any Luck....

Thanks,

MAK.

Re: Background Color Expression

Thiago_Justen_ — Tue, 04 Sep 2018 10:25:28 GMT

Could you please share a mock up?

Re: Background Color Expression

jonathandienst — Tue, 04 Sep 2018 10:35:44 GMT

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

Re: Background Color Expression

jonathandienst — Tue, 04 Sep 2018 10:41:51 GMT

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

)

Re: Background Color Expression

khanashique — Tue, 04 Sep 2018 10:52:23 GMT

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.

Re: Background Color Expression

khanashique — Tue, 04 Sep 2018 11:05:25 GMT

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.

Re: Background Color Expression

khanashique — Tue, 04 Sep 2018 11:06:05 GMT

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.

Re: Background Color Expression

khanashique — Tue, 04 Sep 2018 11:25:54 GMT

Hi Jonathan,

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

Thanks,

MAK.

Re: Background Color Expression

khanashique — Tue, 04 Sep 2018 14:15:58 GMT

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.

Re: Background Color Expression

khanashique — Wed, 05 Sep 2018 04:55:55 GMT

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.