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

    Filter Condition to insert 1 or 0

    Wolverine .

      Comrades,

      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
          Dimitri.B

          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.