Text Object Color

[#1]

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.

[Florentin74]

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

[#3]

Hi,

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

[#4]

Hi,

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

[stevedark]

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

[Florentin74]

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

[#7]

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.

[vinieme12]

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

)))))

[#9]

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

[stevedark · Accepted solution]

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.