topic Re: coloring a text box if no value in QlikView

coloring a text box if no value

Wed, 27 Sep 2017 12:53:06 GMT

I have created a text box with below calucation to determine color.

if(
Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Act"}, Month = {$(=max(Month))}, BusinessType =>} Amount)
<
Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Target"}, Month = {$(=max(Month))}, BusinessType =>} Amount)
,
RGB (80,210,90),RGB (204,0,0))

I would like to extend this to say that if either 1st or 2nd argument has no value it should be greyed out (RGB (200,200,200))

I have tried to add If(ISNULL....but I am not gettiing it right. It could be that I have paranthesis etc. in wrong place.

Anyone that can help me rewrite this in the correct way?

Re: coloring a text box if no value

jonvitale — Wed, 27 Sep 2017 13:09:59 GMT

Carl, a couple questions and a suggestion:

1) What exactly is $(vSetStdWarrPmtComplete) doing in your code? I've simply yet to come across any examples of simply using a variable, alone, as a modifier. I'm genuinely curious.

2) What does the background look like if you run it as it is currently written?

3) Is the problem that it cannot calculate an average because there is no data (after selections) to perform a calculation on? Like, if the user made selections that eliminated "Act" or "Target" from the set, then your set analysis would come up empty. If that's the case, could you do something simple like:

Count({<$(vSetStdWarrPmtComplete), TimeType = {"Act"}, Month = {$(=max(Month))}, BusinessType =>} Amount) > 0

and

Count({<$(vSetStdWarrPmtComplete), TimeType = {"Target"}, Month = {$(=max(Month))}, BusinessType =>} Amount) > 0

and

(

Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Act"}, Month = {$(=max(Month))}, BusinessType =>} Amount)
<
Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Target"}, Month = {$(=max(Month))}, BusinessType =>} Amount)

)

Re: coloring a text box if no value

Anil_Babu_Samineni — Wed, 27 Sep 2017 13:13:59 GMT

Try this?

if(
Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Act"}, Month = {$(=max(Month))}, BusinessType =>} Amount) <
Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Target"}, Month = {$(=max(Month))}, BusinessType =>} Amount) , RGB (80,210,90),

If(IsNull(Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Act","Target"}, Month = {$(=max(Month))}, BusinessType =>} Amount)), RGB(200,200,200),

RGB (204,0,0)))

Re: coloring a text box if no value

effinty2112 — Wed, 27 Sep 2017 13:21:43 GMT

Hi Carl,

Maybe wrap your expression by an if statement that tests for either of the sums to be zero:

if(Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Act"}, Month = {$(=max(Month))}, BusinessType =>} Amount)

Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Target"}, Month = {$(=max(Month))}, BusinessType =>} Amount)

=0,RGB (200,200,200)

if(

Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Act"}, Month = {$(=max(Month))}, BusinessType =>} Amount)

Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Target"}, Month = {$(=max(Month))}, BusinessType =>} Amount)

RGB (80,210,90),RGB (204,0,0))

)

Good luck

Andrew

Re: coloring a text box if no value

Wed, 27 Sep 2017 13:23:14 GMT

the Box is still Green although "Act" has no value.

TimeType = {"Act","Target"},

Does this not mean Act "AND" Target is Null. I need Act OR Target

Re: coloring a text box if no value

Anil_Babu_Samineni — Wed, 27 Sep 2017 13:24:50 GMT

Then this?

if(
Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Act"}, Month = {$(=max(Month))}, BusinessType =>} Amount) <
Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Target"}, Month = {$(=max(Month))}, BusinessType =>} Amount) , RGB (80,210,90),

If(IsNull(Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Act"}> + <TimeType = {"Target"}>, Month = {$(=max(Month))}, BusinessType =>} Amount)), RGB(200,200,200),

RGB (204,0,0)))

Re: coloring a text box if no value

Wed, 27 Sep 2017 13:31:12 GMT

Seems to be an error in your last formula

Andrews formula below gives the desired output, although your approach looks nicer (if I could make it to work)

Re: coloring a text box if no value

Wed, 27 Sep 2017 13:36:30 GMT

Hello Andrew.

thanks for taking time to answer.

Your approach is working in this case.

However I would prefer a IS(Null function as Actual or Target in fact could be 0 for some KPIs on my dashboard which would make your suggestion invalid.

Re: coloring a text box if no value

effinty2112 — Wed, 27 Sep 2017 16:03:42 GMT

Hi Carl,

Maybe I misunderstand but my suggestion will return RGB (200,200,200) if either sum equals 0.

ab = 0 => a=0 or b=0

Cheers

Andrew

Re: coloring a text box if no value

Wed, 27 Sep 2017 16:10:49 GMT

1st let me say that I have had no previous experience from Qlikview or any other BI tool before starting to build this application so it may not be built in the most optimal way. Everything I know about coding/writing expressions etc. I have leart from this forum and Youtube

1. I have about 100 KPIs in my dashboard. Some which based on manual input from our organisation while others are based on data from our financial consolidation system etc.

Let vSetStdWarrPmtComplete = 'FSItem = {"KPIQCS1"}';

This is simply just a renaming of one of the manual KPIs I load into my dashboard.

I'm using this structure for all KPI's as other KPI's could be defined as

Let vSetOverdue= 'FSItem = {"FS 1529011110"} + {"FS 1529011120"} + {"FS 1529011130"} + {"FS 1529011140"} + {"FS 1529011150"} + {"FS 1529011160"} + {"FS 1529011210"} + {"FS 1529011220"} + {"FS 1529011230"} + {"FS 1529011240"} + {"FS 1529011250"} + {"FS 1529011260"}';

I therfore found it easire to to create those variables so my expressions would be easier to write. right or wrong I don't know...

2. Green

3. I will look into it tomorrow

Re: coloring a text box if no value

Wed, 27 Sep 2017 16:34:34 GMT

Lets say we have a Performance index that is Accident Rate.

Target for this should be 0 as no accidents should exist.

In Januari number of accidents reported was 2 which means the box should be red

In Februari number of accidents was also 2 but responsible person never reported this in the dashboard leaving this cell Blank. Then I want the box to be greyed out as the cell in the source data are blank.

In your case both scenarios would make the box grey as Target is set to be 0, right?

Re: coloring a text box if no value

effinty2112 — Wed, 27 Sep 2017 16:55:25 GMT

Hi Carl,

I don't know what any of what you've just written means because I know nothing about your business or your QV data model. All I know is that you've given us two expressions that you want to use as part of a further expression to define a colour:

Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Act"}, Month = {$(=max(Month))}, BusinessType =>} Amount)

and

Sum({<$(vSetStdWarrPmtComplete), TimeType = {"Target"}, Month = {$(=max(Month))}, BusinessType =>} Amount)

Do you want grey when either one of these equals zero?

If so you want grey when the product of these sums equals zero.

Kind regards

Andrew

Re: coloring a text box if no value

Wed, 27 Sep 2017 17:30:37 GMT

I want to grey out when either of those are null (no values have been reported for either act or target).

So Act or Target= 0 could be correct but Act or Target = "missing value" is not correct and then I want color to be grey.

Re: coloring a text box if no value

vkish16161 — Wed, 27 Sep 2017 17:36:18 GMT

If you are doing it in QS

you can try Alt (Measure, rgb(200,200,200))

Re: coloring a text box if no value

jonvitale — Thu, 28 Sep 2017 12:58:56 GMT

Carl,

I'm fairly new to Qlik myself. I'm not sure if the set analysis in variables is good practice or not. It's an interesting idea as we tend to use the same set analyses multiple times. I am only starting to get comfortable with $-expansion and using string expressions for variables, so I tend to keep things as clear and open as possible.

By the way, if you are still planning on trying my suggestion, the full way I would do it would be (in pseudocode)...

If (count(set1) > 0 and count(set2) > 0,

rgb(200, 200, 200), // the color you want if there isn't data in both sets

If (avg(set1) < avg(set2), rgb(your color 1), rgb(your color 2)))