topic Re: range and colour select in App Development

range and colour select

joeybird — Wed, 22 Apr 2015 08:45:28 GMT

Hiya

wild card question - Qlik Sense

is it possible that when you use a kpi box, that when you select it a desired section, it will change background or text colour depending on your selection

e.g

target range is <70% any column selected that value is more than 70 % the text or background will change from black to red

the same question then applies to a bar chart - Jan - Dec

that month columns will be green for within range..... and some month columns will be red to show over the range

Please help

Kind Regards

joeybird

Re: range and colour select

kangaroomac — Wed, 22 Apr 2015 09:01:52 GMT

Hi Joanna,

For a bar chart, you could add your logic as an expression in Appearance -> Colours and legend -> Untick Auto and Choose By Expression from the drop down.

The KPI Object does not seem to have as many options for appearance yet (done using V 1.01).

Re: range and colour select

joeybird — Wed, 22 Apr 2015 09:06:39 GMT

That's great for a bar graph thank you

can you please send me an example line of an expression text, to use

as easier for me to work out logic how done, and then can amend for my requirements.

Kind Regards

Joeybird

Re: range and colour select

kangaroomac — Wed, 22 Apr 2015 09:12:44 GMT

The expression I used in the example above is: IF([Region Name] = 'International', Green(), Blue())

In your case you'll probably use something similar to: IF((Sum(Numerator) / Sum(Denominator)) > 0.7, Red(), Black())

Re: range and colour select

joeybird — Wed, 22 Apr 2015 09:14:54 GMT

that's fab!

thank you x

Kind Regards

Joeybird

Re: range and colour select

joeybird — Thu, 23 Apr 2015 07:46:52 GMT

Hiya

I don't know if you can help a little further

I have used your code successfully as below

IF(((TargetValue)) < (Target), Red(), Green())

however I would like to change the < part

as some selections need to be > e.g 50%

I have another column that in SQL represents GT (greater than) or LT (less than)

Is there a way of altering the code that if LT is selected then it knows proceed with the calculation?

Kind Regards

Joeybird

Re: range and colour select

kangaroomac — Thu, 23 Apr 2015 07:53:15 GMT

Hi Joeybird,

Would an AND work?

i.e. IF((([TargetValue])) < ([Target]) AND [Selector] = 'LT', Red(), Green())

Re: range and colour select

joeybird — Thu, 23 Apr 2015 08:19:33 GMT

Hiya

no because the < is still in the code

Is there a way please the selector column will hopefully represent this when a selection is made?

Kind Regards

Joeybird

Re: range and colour select

kangaroomac — Thu, 23 Apr 2015 08:28:56 GMT

You got me confused.

If you type in pseudo code what you want I may be able to assist.

i.e. IF This Then

That

Else

Something Else

Re: range and colour select

joeybird — Thu, 23 Apr 2015 08:46:44 GMT

Hiya

want to write something like

IF((([TargetValue]) AND [TargetComparator] = 'LT'([Target]) , Red(), Green())

or write something like

IF((([TargetValue]) AND [TargetComparator] = 'LT'([Target]) , Red(), Green())

then else ((([TargetValue]) AND [TargetComparator] = 'GT'([Target]) , Red(), Green())

Kind Regards

Joeybird

Re: range and colour select

kangaroomac — Thu, 23 Apr 2015 08:50:49 GMT

What is contained in:

[TargetValue]
[TargetComparator]
and [Target]

Re: range and colour select

joeybird — Thu, 23 Apr 2015 08:55:50 GMT

Hiya

[TargetValue] e.g 0.76 (80%)
[TargetComparator] GT or LT ........In this case any TargetValue less than (LT) 0.80 then it needs to go red

note sometimes it needs to change to GT 50% for some targets

and [Target] e.g 0.80

Kind Regards

Joeybird

Re: range and colour select

joeybird — Thu, 23 Apr 2015 08:58:16 GMT

Hiya

top line needs to be just

[TargetValue] e.g 0.76

Kind Regards

Joeybird

Re: range and colour select

kangaroomac — Thu, 23 Apr 2015 09:10:07 GMT

I changed the fieldname [TargetValue] to [Value]

Maybe something like this would work?

IF((

([Value] < [Target] AND [TargetComparator] = 'LT') OR

([Value] > [Target] AND [TargetComparator] = 'GT))

, Red(), Green())

Re: range and colour select

joeybird — Thu, 23 Apr 2015 09:16:05 GMT

Hiya

keeps coming up with error in expression

have changed field names

:+(

Re: range and colour select

kangaroomac — Thu, 23 Apr 2015 09:22:18 GMT

Other than looking at the actual .qvf, only advise I can give you is try simplifying your expression.

i.e.

IF((

([Value] < [Target] AND [TargetComparator] = 'LT')

// OR([Value] > [Target] AND [TargetComparator] = 'GT))

, Red(), Green())

If you still get an error, comment out the AND part (being mindful of your brackets) until you find where the syntax error occurs.

Re: range and colour select

joeybird — Thu, 23 Apr 2015 12:08:24 GMT

Hiya

all working yeah thank you....

here is the code (just encase someone else requires) :+)

IF(([TargetValue] > [Target] AND [TargetComparator] = 'GT')

OR ([TargetValue] < [Target] AND [TargetComparator] = 'LT'), Red(), Green())

thank you xx

Kind Regards

Joeybird

Re: range and colour select

joeybird — Fri, 24 Apr 2015 07:15:31 GMT

Hiya

do you think you can apply the same technique please the formatting of the bar graph?

so it converts dynamically. if I created a new field to recognise format in percentage or number

use similar code below?

IF(([TargetValue] AND [Format] = 'Percentage')

OR ([TargetValue] AND [Format] = 'Number'), %(), Number())

kind Regards

Joeybird