IIF - Newbie

Hi,

just for understanding a simple iif statement:
I like to set a numeric value to 0, if it is smaller than 0.
This expression shall be valid over all dimensions for a specific measure

like Iif([Measures].[Provision Ankauf]<0,0,[Measures].[Provision Ankauf] )

it's not working, Pivottable returns ##VALUE everywheremake sure [Measures].[Provision Ankauf] is returning a number, and is not null.

also to make sure you're not crazy try: IIF(5<0,0,5) and see what happens|||IIF is not supported in SQL Server

use CASE instead|||As Rudy pointed out, you need to translate your VB (actually Jet) syntax to:CASE
WHEN [Measures].[Provision Ankauf] < 0 THEN 0
ELSE [Measures].[Provision Ankauf]
END-PatP|||Just to be ornery:

([Measures].[Provision Ankauf]+abs([Measures].[Provision Ankauf]))/2|||Just to be ornery:Ornery ?!?! That's deviant, low down, and high smellin' !!! I LIKE it!

In reality, the CASE statement is something that the original poster needs to know how to use since it opens all kinds of other possibilities that they'll need someday, but your bit of nifty math is both kinky and effective, so it is lots of fun the play with!

-PatP|||blindman, brilliant as usual!!|||Yeah, he needs to know CASE.

For some reason, possibly (probably) unjustified, I always feel like logic statements are less efficient than formula solutions. Just my bias, though I have to agree that the CASE statement is more readable. Without a comment, somebody looking at my solution wouldn't be able to immediately see its purpose.|||actually, now that i take a closer look, dajm probablu just needs IIF

i think he/she is using access, based on Pivottable and ##VALUE, and simply posted in the wrong forum

also knowing CASE isn't that bad an idea, though, eh