Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
khanashique
Contributor II

Background Color Expression

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.

1 Solution

Accepted Solutions
khanashique
Contributor II

Re: Background Color Expression

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.

18 Replies
yogarajsubraman
New Contributor III

Re: Background Color Expression

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

thiago_justen
Valued Contributor III

Re: Background Color Expression

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.

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
khanashique
Contributor II

Re: Background Color Expression

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.

thiago_justen
Valued Contributor III

Re: Background Color Expression

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

)

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
khanashique
Contributor II

Re: Background Color Expression

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.

thiago_justen
Valued Contributor III

Re: Background Color Expression

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

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
khanashique
Contributor II

Re: Background Color Expression

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

OR

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.

khanashique
Contributor II

Re: Background Color Expression

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

OR

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.

khanashique
Contributor II

Re: Background Color Expression

Hi Thiago,

Any Luck....

Thanks,

MAK.