coloring a text box if no value

[#1]

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?

[jonvitale]

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)

)

[Anil_Babu_Samineni]

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

[effinty2112]

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

[#5]

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

[Anil_Babu_Samineni]

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

[#7]

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)

[#8]

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.

[effinty2112]

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

[#10]

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