Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

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
Highlighted
Valued Contributor III

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

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
Highlighted
Valued Contributor III

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

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

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

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

Highlighted
Valued Contributor III

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

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

Highlighted
MVP
MVP

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

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

Highlighted
New Contributor II

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

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.