topic Re: Re: Color for text box expression outcome in QlikView

Color for text box expression outcome

Thu, 13 Mar 2014 13:07:25 GMT

Hi there,

Currently I am facing problems giving the outcome of my expression in the textbox the right color. The expression shows the realization compared to the budget in %. I wish to make the percentage outcome <0 red and >0. Until now I couldn't get it done and I am out of inspiration. I hope someone could give me a hand. The statement so far is:

=Num((1-((-sum({<[Categorie]= {'DIENST' } >} [Budget Bedrag] )-sum({<[Categorie]= {'LICENTIE' } >} [Budget Bedrag] )-sum({<[Categorie]= {'ONDERHOUD' } >} [Budget Bedrag] )-sum({<[Categorie]= {'OPLEIDING' } >} [Budget Bedrag] )-sum({<[Categorie]= {'Overige omzet' } >} [Budget Bedrag] )-sum({<[Categorie]= {'REIS' } >} [Budget Bedrag] )-sum({<[Categorie]= {'OVERIG' } >} [Budget Bedrag] )))

(-sum({<[Categorie]= {'DIENST' } >} [Bedrag] )-sum({<[Categorie]= {'LICENTIE' } >} [Bedrag] )-sum({<[Categorie]= {'ONDERHOUD' } >} [Bedrag] )-sum({<[Categorie]= {'OPLEIDING' } >} [Bedrag] )-sum({<[Categorie]= {'Overige omzet' } >} [Bedrag] )-sum({<[Categorie]= {'REIS' } >} [Bedrag] )-sum({<[Categorie]= {'OVERIG' } >} [Bedrag] )))
,',#%')

Best Regards,

Roy Covalenco

Re: Color for text box expression outcome

alexandros17 — Thu, 13 Mar 2014 13:11:28 GMT

In the text box properties select "General" tab, then in the "Background" area select color and paste your formula in calculated text box area in this way:

If(myExpression < value, RGB(255,0,0),

If(myExpression = value, RGB(0,255,0),

RGB(0,0,255)

)

hope it helps

Re: Color for text box expression outcome

Thu, 13 Mar 2014 13:23:08 GMT

Thanks for your fast response, it seems that it won't work together with my expression somehow. The "outcome" of my text box is now the RGB value.

Re: Color for text box expression outcome

Thu, 13 Mar 2014 13:59:25 GMT

You need to have an = sign before your expresision

= If .........

Re: Color for text box expression outcome

Thu, 13 Mar 2014 15:13:08 GMT

Thanks, but that was already added by me. The outcome given by the expression provided by Alessandro is "RGB(255,0,0)" when using just <0 and when using both <0 and =0 in the same expression the value is " - ". I hope somebody knows how to solve my problem . Thanks for your advice.

Best Regards,

Roy Covalenco

Re: Color for text box expression outcome

Peter_Cammaert — Thu, 13 Mar 2014 15:32:26 GMT

In a text box, there are three qualities you can change in the Properties dialog:

the text displayed: See General->Foreground->Text. If the above formula works, then leave it as is.
the foreground (text) color: See Font->Color, click the color button and select a fixed color or a Calculated value in the field on the right
the background (text area) color: See General->Background->Color, click the color button and select a fixed color or a Calculatyed value i nthe field on the right. This is what Alessandro suggested. Dit you use that technique?

If you put the expression in one of the two Calculated color fields, there will always be a color, never a '-'.

BTW I think you can change your formula into the following less bulky version:

=Num(1-(sum({<[Categorie]= {'DIENST', 'LICENTIE', 'ONDERHOUD', 'OPLEIDING', 'Overige omzet', 'REIS', 'OVERIG' } >} [Budget Bedrag] )

sum({<[Categorie]= {'DIENST', 'LICENTIE', 'ONDERHOUD', 'OPLEIDING', 'Overige omzet', 'REIS', 'OVERIG' } >} [Bedrag] ))
,',#%')

Edit: further simplified the above expression. Replace myExpression both times with this expression but first strip the leading equal sign and the Num() call.

Re: Re: Color for text box expression outcome

Peter_Cammaert — Thu, 13 Mar 2014 16:08:39 GMT

See this example. Change the loaded data in the script and see what happens with the two text boxes.

Good luck,

Peter

Re: Re: Color for text box expression outcome

Tue, 13 May 2014 10:23:10 GMT

Peter,

I totally missed your responce, either way thanks a lot . It was helpful.

Re: Color for text box expression outcome

Peter_Cammaert — Tue, 13 May 2014 11:24:38 GMT

Glad it was.

Please close the discussion by marking an answer as correct (and some others as helpful). That way community visitors can find topic discussions that do actually contain an answer to their questions (the blue balloon will turn green = answered). Thanks.