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

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

Anonymous — Thu, 06 Aug 2015 16:50:11 GMT

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

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

sinanozdemir — Thu, 06 Aug 2015 16:58:31 GMT

I think what you want to do is:

=If(

[Status]='Open'

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

)

Hope this helps.

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

settu_periasamy — Thu, 06 Aug 2015 16:59:48 GMT

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

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

ramoncova06 — Thu, 06 Aug 2015 17:01:32 GMT

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

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

maxgro — Thu, 06 Aug 2015 17:12:30 GMT

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

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

Anonymous — Fri, 07 Aug 2015 10:23:12 GMT

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.