I have an IF statement where I believe I need to incorporate an AND or an OR so that the first criteria doesn't overwrite the other criteria:

 Volumes2/A20=IF CreatedStatus EQ 'CREATED' THEN '1-CREATED' ELSE IF Previously EQ 'NA' AND Currently EQ 'ACTIVE' THEN '2-ACTIVATED' ELSE IF Previously EQ 'ACTIVE' AND Currently EQ 'DEACTIVATED' THEN '3-DEACTIVATED' ELSE IF Previously EQ 'DEACTIVATED' AND Currently EQ 'ACTIVE' THEN '4-REACTIVATED' ELSE 'UPDATED INFO' 

The problem is that if CreatedStatus comes back as CREATED then the ACTIVE status does not get counted. I believe I need to say something like

 IF CreatedStatus EQ 'CREATED' THEN '1-CREATED' OR ELSE IF Previously EQ 'NA' AND Currently EQ 'ACTIVE' THEN '2-ACTIVATED'... 

but the OR ELSE part is not working. I can only find instructions on using compound expressions with WHERE statements. Is it possible to do something like an OR ELSE in a define?
Thanks.

This message has been edited. Last edited by: TRue,

The order of the IF sections is critical. As soon as one section evaluates to True, the IF is complete and the ELSE sections are ignored.

Of course if you are just testing the same field(s) in each section, then the order isn't as important (just put the most likely first to speed up the evaluation). In your case you are testing different fields, so the order matters.

If '2-ACTIVATED' is more important then '1-CREATED' then that section must come first...

IF Previously EQ 'NA' AND Currently EQ 'ACTIVE' THEN '2-ACTIVATED' ELSE IF CreatedStatus EQ 'CREATED' THEN '1-CREATED' ELSE...

Yes, you can use compound expressions in defines. How to build a compound expression is pretty much the same ... whether in a where for data selection or in a define to get a field value, etc.

If CreatedStatus comes back as CREATED then your Volumes2 will be set to '1-CREATED' (as you state). The condition is satisfied, so no other logic is applied.

I am not clear on what you mean by " ACTIVE status does not get counted". Will you explain a little more?

Thanks Jim,
Ordering the logic makes sense, however even if I put the CreatedStatus criteria as the last criteria I end up wrong, because in this case the status can be BOTH Created and Activated.
If CreatedStatus EQ 'CREATED' then '1-CREATED', but *ALSO* If Previously EQ 'NA' AND Currently EQ 'ACTIVE' THEN '2-ACTIVATED', etc.
If I leave it in this order I am getting a count under 1-CREATED only, but it was also 2-ACTIVATED in the same period. If I switch the order so that 1-CREATED is at the end of the statement then I only get a count for 2-ACTIVATED.
So the dilemma is how I can make it say both, when both are true.

You have 3 fields to work with: CreatedStatus, Previously, and Currently

And 6 cases:

CreatedStatus  Previously  Currently
-------------  -----------  ---------
CREATED        NA           ACTIVE
CREATED        ACTIVE       DEACTIVATED
CREATED        DEACTIVATED  ACTIVE
not CREATED    NA           ACTIVE
not CREATED    ACTIVE       DEACTIVATED
not CREATED    DEACTIVATED  ACTIVE

Sounds like you need to test for all three values in a complex IF containing all 6 cases.

Thanks again, Jim. I thought this would do it, but it didn't:

IF CreatedStatus EQ 'CREATED' AND Previously EQ 'NA' AND Currently EQ 'ACTIVE' THEN '1-CREATED' 
ELSE IF CreatedStatus EQ 'CREATED' AND Previously EQ 'ACTIVE' AND Currently EQ 'DEACTIVATED' THEN '1-CREATED' 
ELSE IF CreatedStatus EQ 'CREATED' AND Previously EQ 'DEACTIVATED' AND Currently EQ 'ACTIVE' THEN '1-CREATED' 
ELSE IF CreatedStatus NE 'CREATED' AND Previously EQ 'NA' AND Currently EQ 'ACTIVE' THEN '2-ACTIVATED' 
ELSE IF CreatedStatus NE 'CREATED' AND Previously EQ 'ACTIVE' AND Currently EQ 'DEACTIVATED' THEN '3-DEACTIVATED' 
ELSE IF CreatedStatus NE 'CREATED' AND Previously EQ 'DEACTIVATED' AND Currently EQ 'ACTIVE' THEN '4-REACTIVATED' 
ELSE 'NA'

It still won't count Activated accounts, if they have been 'Created' in the same period. The problem is still that multiple statuses may be true at the same time.
It works only for the 2-ACTIVATED, 3-DEACTIVATED, and 4-REACTIVATED columns. They are all from the same table in my join, though. However as soon as CREATED is true (defined from a separate table) it will not provide a count for the other 3 columns.
Account_____Cre__Act__Dea__Rea
-----------------------------------------------------------
987987987 ___1___0____0____0 --> wrong, acct was also Activated during period
654654654 ___0___1____1____0

Is it possible my formula just needs a small modification just to say 'OR' or 'AND' to allow multiple statuses to be true even when Created is found?

It looks like you want to count some entries twice.

I think you may want something like this:

CreateCount   /I9 = IF CreatedStatus EQ 'CREATED' THEN 1 ELSE 0;
ActiveCount   /I9 = IF Previously EQ 'NA' AND Currently EQ 'ACTIVE' THEN 1 ELSE 0;
DeactiveCount /I9 = IF Previously EQ 'ACTIVE' AND Currently EQ 'DEACTIVATED' THEN 1 ELSE 0;
ReactiveCount /I9 = IF Previously EQ 'DEACTIVATED' AND Currently EQ 'ACTIVE' THEN 1 ELSE 0;

TABLE FILE ???
SUM
   CreateCount
   ActiveCount
   DeactiveCount
   ReactiveCount
BY
   Account
END

Funny enough I had just started down this line of thought before I read your response.
At first try, in the 4 new columns it counted Created only, nothing for the rest. I added in the 2 fields used to create the Previously and Currently fields to check why it didn't work... and then the counts worked. VERY STRANGE, but THANK YOU JIM! With your help and 3 days of persistence it finally works.
Happy Friday!
Edit: I have since found out there is a problem with the fields I am using to create the Previously and Currently defines - they are corrupt in some way. Hence the strange behavior.

This message has been edited. Last edited by: TRue,