Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!!!!




5 Replies
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


talk is cheap, supply exceeds demand
maxgro
MVP
MVP

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)))

;

Not applicable
Author

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.


Not applicable
Author

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.

Not applicable
Author

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.