5 Replies Latest reply: Sep 29, 2015 12:56 PM by Kimmie Wong RSS

    If statement

      Hi All,

       

      I have a requirement to build logic. The conditions are;

      My conditions are as follows:

      Sales Date (SD) should be before 2010

      AND Acquisitiondate(AD)  should be 1 year after the respective Sales date

      AND Acquisitiondate (AD)    should be within 2010

      AND Code should be 10 OR Costdate (CD) should have been within 24 months from the date (SD) 

       

      Below is the code, please let me know if it is right:

      IF(Floor(SD)<='12/31/2010'
      AND Floor(AD)> Floor(Year(SD))+ 1
      AND Floor(AD)>='01/01/2015' AND Floor(AD)<='12/31/2015'
      AND (Code) ='10')

      OR Floor(AD)> SD+24, '1', '0') as ABC ( Not sure how to code the last part Acquisitiondate (AD) should have been within 24 months from the Sales date (SD))


       

      [Sales Date (SD) should be before 2010

      AND Acquisitiondate(AD)  should be 1 year after the respective Sales date

      AND Acquisitiondate (AD)    should be within 2010

      AND Code should be 10] This set fetches 3 records


      [Costdate (CD) should have been within 24 months from the date (SD)] This filters to 2 records

       

      I am trying to understand how does the OR operator behave in this case. I have attached sample data. from that should the final  number of records should be????


      HELP is greatly Appreciated!!!!




        • Re: If statement
          Gysbert Wassenaar

          Sales Date (SD) should be before 2010: Floor(SD)<makedate(2010)

          AND Acquisitiondate (AD) should be 1 year after the respective Sales date: AND AD=AddYears(SD,1)

          AND Acquisitiondate (AD) should be within 2010: AND Year(AD) = 2010

          AND Code should be 10: AND Code = 10

          OR Costdate (CD) should have been within 24 months from the date (SD): OR Fabs(CD-SD)<365*2

          • Re: If statement
            Massimo Grossi

            LOAD SD, AD,

                 CD, Code

            FROM

            [New Test.xlsx]

            (ooxml, embedded labels, table is Sheet1)

            Where

            Year(SD) < 2010

            and AD=AddYears(SD, 1)

            and Year(AD)=2010

            and (Code=10 or (CD<=AddMonths(SD,24) and CD>=AddMonths(SD,-24)))

            ;

            • Re: If statement

              Thanks for your replies. I am trying to create fields for Denominator and Numerator.

              My Denominator has the above conditions;

              Sales Date (SD) should be before 2010

              AND Acquisitiondate(AD)  should be 1 year after the respective Sales date

              AND Acquisitiondate (AD)    should be within 2010

              AND there should be at least 2 AD for a PKID


              My Numerator has additional conditions;

              Sales Date (SD) should be before 2010

              AND Acquisitiondate(AD)  should be 1 year after the respective Sales date

              AND Acquisitiondate (AD)    should be within 2010

              AND there should be at least 2 AD for a PKID

              AND Code should be 10 OR 11 OR 12 OR Costdate (CD) should have been within 24 months from the date (SD)

              AND ICode should be 333 OR 444 OR Costdate (CD) should have been within 24 months from the date (SD)

              AND Code should be 14 OR 15


              Technically my numerator should fetch less number of values than my denominator as it is same conditions plus some more. However it is pulling more row. Excel data attached.


              • Re: If statement

                Can somebody please help?

                Sales Date (SD) should be before 2010

                AND Acquisitiondate(AD)  should be 1 year after the respective Sales date

                AND Acquisitiondate (AD)    should be within 2010

                AND there should be at least 2 AD for a PKID


                My Numerator has additional conditions;

                Sales Date (SD) should be before 2010

                AND Acquisitiondate(AD)  should be 1 year after the respective Sales date

                AND Acquisitiondate (AD)    should be within 2010

                AND there should be at least 2 AD for a PKID

                AND Code should be 10 OR 11 OR 12 OR Costdate (CD) should have been within 24 months from the date (SD)

                AND ICode should be 333 OR 444 OR Costdate (CD) should have been within 24 months from the date (SD)

                AND Code should be 14 OR 15


                Technically my numerator should fetch less number of values than my denominator as it is same conditions plus some more. However it is pulling more row. Excel data attached.

                • Re: If statement

                  Hi Experts,

                   

                  I need some more help with the logic.  Looking forward to your inputs!

                   

                  Sales Date (SD) should be before 2010

                  AND Acquisitiondate(AD)  should be 1 year after the respective Sales date

                  AND Acquisitiondate (AD)    should be within 2010

                  AND there should be at least 2 AD for a PKID


                  My Numerator has additional conditions;

                  Sales Date (SD) should be before 2010

                  AND Acquisitiondate(AD)  should be 1 year after the respective Sales date

                  AND Acquisitiondate (AD)    should be within 2010

                  AND there should be at least 2 AD for a PKID

                  AND Code should be 10 OR 11 OR 12 OR Costdate (CD) should have been within 24 months from the date (SD)

                  AND ICode should be 333 OR 444 OR Costdate (CD) should have been within 24 months from the date (SD)

                  AND Code should be 14 OR 15


                  Technically my numerator should fetch less number of values than my denominator as it is same conditions plus some more. However it is pulling more rows. Excel data attached.