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

Date range within IF statement (greater than/less than)


Hi all,

I am having an issue defining a date range within an expression IF statement.

Basically, I want the field ID# to display as follows:

If the Status field is 'Open' or if the Closure Date is in the last 12 months.

My IF statement looks like this:

=If([Status]='Open', ID#,

If(MonthStart(AddMonths(Today([Closure Date_Month-Year]),-12))<=[Closure Date_Month-Year]<=MonthEnd(AddMonths(Today([Closure Date_Month-Year]),-1)), ID#))

The syntax seems to be saccepted, but it is not producing any results.

Any help would be appreciated.

Thanks

1 Solution

Accepted Solutions
sinanozdemir
Specialist III
Specialist III

I think what you want to do is:

=If(

     [Status]='Open'

     Or

     (MonthStart(AddMonths(Today(),-12))>=[Closure Date_Month-Year] and [Closure Date_Month-Year]<=MonthEnd(AddMonths(Today(),-1))), ID#,

)

Hope this helps.

View solution in original post

5 Replies
sinanozdemir
Specialist III
Specialist III

I think what you want to do is:

=If(

     [Status]='Open'

     Or

     (MonthStart(AddMonths(Today(),-12))>=[Closure Date_Month-Year] and [Closure Date_Month-Year]<=MonthEnd(AddMonths(Today(),-1))), ID#,

)

Hope this helps.

settu_periasamy
Master III
Master III

Hi,

Can you try this..

=If([Status]='Open' or

(MonthStart(AddMonths(Today([Closure Date_Month-Year]),-12))<=[Closure Date_Month-Year] and

  [Closure Date_Month-Year]<=MonthEnd(AddMonths(Today([Closure Date_Month-Year]),-1))),ID#)

ramoncova06
Specialist III
Specialist III

you are not able to use today() with anything else than 0,1,2

try with this

=If([Status]='Open', ID#,

If(addMonths(Today(),-12))>=[Closure Date_Month-Year], ID#))

maxgro
MVP
MVP

I think Sinan's answer is correct (maybe switching <= with >=)

Regarding you solution, I think it can work

if(Status='Open', ID#,

if(......<=<=[Closure Date_Month-Year] and [Closure Date_Month-Year]<=....., ID#

))

but

Today() not Today([Closure Date_Month-Year])

in the 2nd if you need to condition for your Closure Date

Anonymous
Not applicable
Author

Thanks all for your help.

Both solutions from Sinan and Ramon worked.

Correct Massimo, aside from the <= which had to swapped.

The root of the error was having the field name within the operator today(), as you pointed out Ramon.

Thanks again.