How to ignore unselected field states in if statement

[#1]

Right now I have a text box which displays using the following condition:

=If(Round(Sum(ObligatedAmount/1000),1000)=Round(Sum(Amount),1000) and Max(fiscal_year)<2014,0,1)

It works great, but unfortunately this means it displays not only when fiscal_year is greater than 2014, but also when there simply isn't a selection, like right when the QlikView document opens.  How do I get it to ignore when fiscal_year is unselected?

[vgutkovsky]

You can add another simple condition: getselectedcount(fiscal_year)>0

Regards,

Vlad

[#3]

Like this?

=If(Round(Sum(ObligatedAmount/1000),1000)=Round(Sum(Amount),1000) and Max(fiscal_year)<2014 and GetSelectedCount(fiscal_year)>0,0,1)

That doesn't seem to work for me.

[vgutkovsky · Accepted solution]

Try this: If(Round(Sum(ObligatedAmount/1000),1000)=Round(Sum(Amount),1000) and (Max(fiscal_year)<2014 or GetSelectedCount(fiscal_year)=0),0,1)

[#5]

Yep!  That worked!  Thanks Vlad!

[#6]

Ugh, for reasons unrelated to the construction of the equation that function no longer works.  Now I'm trying to make the following:

IF(1.01>Absolute Value((Sum(ObligatedAmount/1000)/Sum(Amount))>.99 and Max(fiscal_year)<2014,0,1)

How do I achieve the Absolute Value condition?

[swuehl]

Using fabs() function?

[#8]

I'm getting an error in expression message for the following:

=if(1.01>fabs(Sum(ObligatedAmount/1000)/Sum(Amount))>.99 and Max(fiscal_year)<2014,0,1)

That said, fabs(Sum(ObligatedAmount/1000)/Sum(Amount) is returning a correct value.  It's just not behaving according the the above statement - I'll get values of 1.000000004 which still display.

[swuehl]

Ah, I overlooked that you probably want something like this

=If(

     1.01> fabs( Sum(ObligatedAmount/1000) / Sum(Amount) )

               and

     fabs( Sum(ObligatedAmount/1000) / Sum(Amount ) ) >.99

               and

     Max(fiscal_year)<2014

,1

,0

)

[#10]

Thanks swuehl!  Had a few tweaks to get around some data-based nonsense, but it looks like we've got it under control now.