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: 
Anonymous
Not applicable

If Statement with Date Format

I have a current field called CloseDate which is formatted as YYYY-MM-DD

I am trying to write the if statement in my load script below to categorize records into new quarters by CloseDate

    if(Date(CloseDate) >= 1/2/2018 AND Date(CloseDate) <= 4/30/2018, 20191,

    if(Date(CloseDate) >= 5/1/2018 AND Date(CloseDate) <= 7/31/2018, 20192,

    if(Date(CloseDate) >= 8/1/2018 AND Date(CloseDate) <= 10/31/2018, 20193,

    if(Date(CloseDate) >= 11/1/2018 AND Date(CloseDate) <= 1/31/2019, 20194, Null())))) as NewQuarter

I've also tried this with the date conditional in the same format as the CloseDate field

    if(CloseDate >= 2018-02-01 AND CloseDate <= 2018-04-30, 20191,

    if(CloseDate >= 2018-05-01 AND CloseDate <= 2018-07-31, 20192,

    if(CloseDate >= 2018-08-01 AND CloseDate <= 2018-10-31, 20193,

    if(CloseDate >= 2018-11-01 AND CloseDate <= 2019-01-31, 20194, Null())))) as NewQuarter

Netiher is working for me. Any suggestions?

1 Solution

Accepted Solutions
sunny_talwar

Try this may be

    if(CloseDate >= MakeDate(2018, 2, 1) and CloseDate <= MakeDate(2018, 4, 30), 20191,

    if(CloseDate >= MakeDate(2018, 5, 1) and CloseDate <= MakeDate(2018, 7, 31), 20192,

    if(CloseDate >= MakeDate(2018, 8, 1) and CloseDate <= MakeDate(2018, 10, 31), 20193,

    if(CloseDate >= MakeDate(2018, 11, 1) and CloseDate <= MakeDate(2019, 1, 31), 20194, Null())))) as NewQuarter

View solution in original post

1 Reply
sunny_talwar

Try this may be

    if(CloseDate >= MakeDate(2018, 2, 1) and CloseDate <= MakeDate(2018, 4, 30), 20191,

    if(CloseDate >= MakeDate(2018, 5, 1) and CloseDate <= MakeDate(2018, 7, 31), 20192,

    if(CloseDate >= MakeDate(2018, 8, 1) and CloseDate <= MakeDate(2018, 10, 31), 20193,

    if(CloseDate >= MakeDate(2018, 11, 1) and CloseDate <= MakeDate(2019, 1, 31), 20194, Null())))) as NewQuarter