Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

dgiannicola
New Contributor II

Qlik sense desktop manage colours in expression panel

Hi, I need to define an expression that change colour when a calculation is a 80% or 60 or 40%; code is:

if(count(((if(AFFECTED_ITEM='Dollar Universe' and STATUS='open',NUMBER)*0.01)-1)) >= 70%, green(), yellow())

AFFECTED_ITEM= DB column header

STATUS = DB column header

NUMBER = DB column header

if I use this code :

((count(if(AFFECTED_ITEM='Dollar Universe' and STATUS='open',NUMBER)))*0.01)-1

i get percentage value (93% for example).

But when I try to apply the first code to change colour on different percentages, expression panel send me an error in script.

Can you help me ?

Regards

Daniele

17 Replies
MVP & Luminary
MVP & Luminary

Re: Qlik sense desktop manage colours in expression panel

Hi Daniele,

the problem here is rather simple - don't use formatted % numbers in expressions, use the "raw" decimal numbers instead:

if(count(((if(AFFECTED_ITEM='Dollar Universe' and STATUS='open',NUMBER)*0.01)-1)) >= 0.70, green(), yellow())


Also, if your data can get big, try to avoid IF() functions within aggregation functions like COUNT() - these are very slow. Use Set Analysis instead:


if(

   count({< AFFECTED_ITEM={'Dollar Universe'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.70

   , green(), yellow()

)


Once your get used to the syntax, the expression will even look better to you.


You can learn about Set Analysis from a variety of sources, including my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense or from my half-day lecture about Set Analysis and AGGR at the Masters Summit for Qlik.  


cheers,

Oleg Troyansky

dgiannicola
New Contributor II

Re: Qlik sense desktop manage colours in expression panel

Hi Oleg,

      thank you very much, code is perfect and it works!

Regards

Daniele

MVP & Luminary
MVP & Luminary

Re: Qlik sense desktop manage colours in expression panel

Glad I could help... So I guess it's a "Correct" answer , not just Helpful?

Cheers,

Oleg Troyansky

Highlighted
dgiannicola
New Contributor II

Re: Qlik sense desktop manage colours in expression panel

Hi Oleg,

     can you tell me how to use if, then, else structure to implement condition like:

if > 0.90 then

     condition

else > 0.80 and <0.90 then

     condition

and if

thank you so much.

Daniele

MVP & Luminary
MVP & Luminary

Re: Qlik sense desktop manage colours in expression panel

IF( field > 0.90, then-value ,

IF(field < 0.80, then-value, else-value))

dgiannicola
New Contributor II

Re: Qlik sense desktop manage colours in expression panel

Hi Oleg,

     I'm writing:

if(

   count({< AFFECTED_ITEM={'Ged'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.90 then

   , rgb(41,105,52), rgb(190,17,16)

{ elseif(

    count({< AFFECTED_ITEM={'Ged'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.80 and <= 0.90 then

   , rgb(220,181,43), rgb(190,17,16)

)}

)

end if

this script send me an error in expression; what do you think about ?

regards

Daniele

MVP & Luminary
MVP & Luminary

Re: Qlik sense desktop manage colours in expression panel

Hi Daniele,

Please review the syntax of the IF function in the Help Section. It doesn't allow words like "then", "else", or "Elseif". Instead, you use commas to separate the different parameters.

You can use up to 3 parameters in each IF:

1. The condition

2. The value if the condition is TRUE

3. (optional) the value if the condition is false.

So, in the beginning of your formula, you use all 3 parameters:

if(

   count({< AFFECTED_ITEM={'Ged'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.90 ,   // not then

   , rgb(41,105,52), rgb(190,17,16)

After the two RGB functions, you already specified both the TRUE and the FALSE values. There is no more IF available after that. If you need to check another condition, then replace the second RGB() with another if function.

cheers,

Oleg Troyansky

dgiannicola
New Contributor II

Re: Qlik sense desktop manage colours in expression panel

Hi Oleg,

     thenk you for your attention. I'm trying this:

if(

   count({< AFFECTED_ITEM={'Ged'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.90

   , rgb(41,105,52), rgb(190,17,16)

)

if(

   count({< AFFECTED_ITEM={'Ged'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.80 and <= 0.90

   , rgb(220,181,43), rgb(169,54,41)

)

but in expression window error is "Error in expression".

What do you think about?

Regards

Daniele

MVP & Luminary
MVP & Luminary

Re: Qlik sense desktop manage colours in expression panel

Again, in this formula you have two separate expressions that could be valid separately, but not together:

if(

   count({< AFFECTED_ITEM={'Ged'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.90

   , rgb(41,105,52), rgb(190,17,16)

)

This is a complete expression. It returns one color if the condition is TRUE and the other color if the condition is false. Nothing else can be added to it.

if(

   count({< AFFECTED_ITEM={'Ged'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.80 and <= 0.90

   , rgb(220,181,43), rgb(169,54,41)

)

This is another complete expression. It's valid by itself, but it cannot be simply attached to the previous expression, it simply won't work.

So, you can use either the first part of the expression, or the second one. If you wanted to combined them together, you'd need to sacrifice one of the RGB functions:

if(

   count({< AFFECTED_ITEM={'Ged'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.90

   , rgb(41,105,52),  // this is removed :rgb(190,17,16)

                                //)

if(

   count({< AFFECTED_ITEM={'Ged'}, STATUS={'open'} >}  NUMBER)*0.01-1 >= 0.80 and <= 0.90

   , rgb(220,181,43), rgb(169,54,41)

)

)