November 3, 2010 at 9:08 am
Ok. I have two expressions that return a sum for me.
#1 =SUM(IIF(Fields!Work_Center.Value="34 MAZAK",1,0))
#2 =SUM(IIF(Fields!Work_Center.Value="34 MAZAK" AND Fields!Operation_Status.Value="O",1,0))
Instead of having yet a third expression I would like to combine them all. I need an IFF expression to give me this:
If #1 and #2 equal 0 than "X"
If #1 >0 and #2 equal 0 than "Done"
If #1 >0 and #2 >0 than "Open"
Is this possible with the expressions I have? I've been messing around for a couple hours now and can't seem to get it right.
November 3, 2010 at 10:06 am
dcase 42479 (11/3/2010)
Ok. I have two expressions that return a sum for me.#1 =SUM(IIF(Fields!Work_Center.Value="34 MAZAK",1,0))
#2 =SUM(IIF(Fields!Work_Center.Value="34 MAZAK" AND Fields!Operation_Status.Value="O",1,0))
Instead of having yet a third expression I would like to combine them all. I need an IFF expression to give me this:
If #1 and #2 equal 0 than "X"
If #1 >0 and #2 equal 0 than "Done"
If #1 >0 and #2 >0 than "Open"
Is this possible with the expressions I have? I've been messing around for a couple hours now and can't seem to get it right.
Your 3 if statements don't really match the expressions you have above so I am not sure I completely understand your question.
What happens when #1 is 0 and #2 > 0?
Can you have any negatives?
You can nest IIF statements
=IIF(#1 = 0 and #2 = 0, "X",IIF(#1 >0 and #2 =0,"Done",IIF(#1>0 and #2 >0,"Open","what ever you want to do when these criteria are not met)))
If these are the only 3 conditions that can happen you can shorten it.
=IIF(#1 = 0 and #2 = 0, "X",IIF(#1 >0 and #2 = 0,"Done",Open))
A lot of people are beginning to use the SWITCH statement rather than nested IIF statements. SWITCH takes the form of (criteria1, response1, criteria2, response2, criteria3,response3, etc)
=SWITCH(#1 = 0 and #2 = 0, "X",#1 >0 and #2 = 0,"Done",#1>0 and #2 >0,"Open")
A SWITCH will return the first response where the criteria evaluates to true, so it is important that only one expression can be true regardless of the situation.
November 3, 2010 at 10:16 am
Got it to work! Had an extra , in my code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply