topic Text Object Color in QlikView

Text Object Color

Fri, 10 Mar 2017 09:02:02 GMT

Hi All,

I am very new to Qlikview so am still getting use to the set analysis side of the tool.

I am trying to change the colour of a text object. As far as i can see the script seems to be ok. Here it is

IF(v.Option.Period ='YTD',

IF ( $(Max.Turnover) <25 and $(Max.Turnover) >= 0,Green(),

IF ( $(Max.Turnover) >=25 and $(Max.Turnover) < 50,Blue(),

IF ( $(Max.Turnover) >=50 and $(Max.Turnover) <= 150, Yellow(),

IF ( $(Max.Turnover) >150 and $(Max.Turnover) <= 200,Red()

)))))

The value of max turnover is '127.8%'. I am not sure if having the '%' is why it is not functioning correctly.

I also noticed that if I change the first IF option from Green to Blue then the text appears in Blue. So it seems like it is only responding to the first option in the nested IF's. I thank anyone in advance if they can assist me in this. many thanks.

Re: Text Object Color

Florentin74 — Fri, 10 Mar 2017 09:18:49 GMT

Hi Ash,

If you want to change the color based on an expression, then right click the text object, click on the back ground color. Use the calculated color, and use expression:

IF(SUM(Revenue)>XXXX,RGB(233,233,233),RGB(212,231,231))

Regards,

Anders

Re: Text Object Color

Fri, 10 Mar 2017 09:33:05 GMT

Hi,

If the Max Turnover is in % it can't equate to the numeric value. Try removing percentage from Max turn over.

Re: Text Object Color

Fri, 10 Mar 2017 09:37:09 GMT

Hi,

try Num(Max.Turnover,0.00)*100 in place of (Max.Turnover).

Re: Text Object Color

stevedark — Fri, 10 Mar 2017 09:39:26 GMT

It may be as simple as you need to have the numbers you are comparing to in decimal form, so 25% = 0.25.

Try changing to 0.25, 0.5, 1.5 and 2 throughout your expression.

Also check the two variables return what you expect, the easiest way to do this is by creating text objects that simply have =$(Max.Turnover)

As premdayal‌ suggests, if the value has the percent in place it may be that it is actually a string, check what happens when you do the following in a text box:

=num($(Max.Turnover), '#,##0')

If it is null then you need to look into your variable and remove whatever it is that is making your number a string.

Perhaps paste the variable code here if you need assistance.

Steve

Re: Text Object Color

Florentin74 — Fri, 10 Mar 2017 09:50:37 GMT

I didn't see your expression...so forget my post :-)..

Re: Text Object Color

Fri, 10 Mar 2017 10:00:31 GMT

Hi Anders, Prem, Steve,

Thanks so much for your quick response. Really appreciate it.

I have tried a few of the suggestions.

Trying this >> =num($(Max.Turnover), '#,##0') returns a 1

The Max.Turnover is made up by the following:-

num(

$(Max.Leavers.All)/ $(Max.HeadCount)

,'#,##0.0%;(#,##0.0)%')

I was wondering is a way of removing the % in my original script without having to change it here.?

Or maybe do:-

$(Max.Leavers.All)/ $(Max.HeadCount)*100

Changing my original script to:-

IF ( $(Max.Leavers.All)/ $(Max.HeadCount)*100 <25 and $(Max.Leavers.All)/ $(Max.HeadCount)*100 >= 0,Green(),

Would this work.

Re: Text Object Color

vinieme12 — Fri, 10 Mar 2017 10:05:33 GMT

A simpler expression would be

IF(v.Option.Period ='YTD',

IF ( $(Max.Turnover) > 150, Red()

IF ( $(Max.Turnover) >= 50,Yellow(),

IF ( $(Max.Turnover) >=25 ,Blue(),

IF ( $(Max.Turnover) >=0,Green()

)))))

Re: Text Object Color

Fri, 10 Mar 2017 12:18:37 GMT

Hi Vineeth,

Thanks for your help. I used a combination of all your suggestions to come up with a solution.

I created a new variable without the % for

num($(Max.Leavers.All)/ $(Max.HeadCount),'#,##0.0%;(#,##0.0)%')

But it would have been nice if I could find a way of using the existing variable and just stripping the % part of it which it seems was causing the problem.

And your expression was definitely simpler.

Thanks

Re: Text Object Color

stevedark — Fri, 10 Mar 2017 12:44:24 GMT

The 1 will be a rounding up. If you change the checking expression to:

=num($(Max.Turnover), '#,##0.00')

You should then see the decimal part.

I would go for the decimal numbers in your expression (0.25 etc.) as I suggested above.

Good luck.

Re: Text Object Color

Fri, 10 Mar 2017 13:54:13 GMT

Thanks for your help. That helped.

Re: Text Object Color

stevedark — Fri, 10 Mar 2017 15:54:20 GMT

No worries, glad it worked out for you.