1 Reply Latest reply on Aug 19, 2012 6:05 PM by Dimitri.B

# Filter Condition to insert 1 or 0

I'm in a flux over a simple filter condition, and have nearly lost all of my neurons figuring out a correct way.

Description: I'm calculating revenue compliance of projects, with the following rule:

1. If Project_Class = A, mark it as "Compliant"

2. For all other Project Classes

a. If Last_Submission_Date is Blank, mark it as "Non-Compliant"

b. If the difference between the Last_Submission_Date and today is greater than 14, mark it as "Non-Compliant"

3. For all other cases, mark it as "Compliant"

The filter condition that I came up with is:

IF([Project Class]='Subscription') THEN 1

ELSEIF([Project Class]!='Subscription' and ISNULL([Project Rev Fcast Last Upd Dt])) THEN 0

ELSEIF([Project Class]!='Subscription' and DATEDIFF('day',[Project Rev Fcast Last Upd Dt],TODAY())>14) THEN 0

ELSE 1 END

I was hoping for a '0' or a '1' as the two values for this filter, but in actuality I'm getting values upto 5 (1, 2, 3, 4, 5). The reason for defining this filter with integers is that I want to sum the total compliant (the 1's) and the total non-compliant (the 0's) projects across a particular department.

Looking for a fresh set of eyes which can help me out here with my filter.

Much appreciated,

• ###### 1. Re: Filter Condition to insert 1 or 0

Without a data sample, using only your description of the desired logic, I think you need to go for a nested IF:

IF([Project Class]='Subscription') THEN 1 //Subscription

ELSE //all other classes

IF ISNULL([Project Rev Fcast Last Upd Dt])

OR DATEDIFF('day',[Project Rev Fcast Last Upd Dt],TODAY())>14 THEN 0

ELSE 1

END

END

I still don't know why you would get anything except 1, 0 or Null, even with your IF statement - you are probably seeing aggregated (summed) results.