Multi-Level If Filter

[rwb139]

Hey everyone,

I am having a problem and need some help with a filter I am trying to create. I am wanting to create a filter that would give a 'y' if the property had been rented 3 quarters ago and a 'n' if not. My data set has values such as lease_from: 1/1/2019 or 2/15/2020 to lease_to: 1/1/2020 or 2/15/2021. My formula is below and for some reason it's bringing up null values instead. Thanks in advance for the help!

if(QuarterStart(AddMonths(today(),-9))>=date(lease_from),

if(QuarterStart(AddMonths(today(),-9))<=date(lease_to),'y',

if(QuarterEnd(AddMonths(today(),-9))>=date(lease_from),

if(QuarterEnd(AddMonths(today(),-9))<=date(lease_to),'y',
),

'n')))

[chrismarlow]

Hi,

Should you be using Date# to convert to a date rather than Date (which formats)?

Date - https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Forma...

Date# - https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Inter...

If your load scrpt sets these up as dates, ideally you would not need either.

Cheers,

Chris.

[rwb139]

@chrismarlow 

Thanks for your response! So with this formula, 

if(QuarterStart(AddMonths(today(),-3))>=date(lease_from) and QuarterStart(AddMonths(today(),-3))<=date(lease_to),'y',

if(QuarterEnd(AddMonths(today(),-3))>=date(lease_from) and QuarterEnd(AddMonths(today(),-3))<=date(lease_to),'y','n'))

I have gotten what I need when using it in a table. However, when I try to create a custom field from it, it tells me "Missing right parenthesis" and if I replace the 'and's with '&' it gives me an incomplete visualization. Any ideas?

[rwb139 · Accepted solution]

Solved it by using this if statement: 

if(QuarterStart(AddMonths(today(),-9))>=date(lease_from),if(QuarterStart(AddMonths(today(),-9))<=date(lease_to),'y','n'),
if(QuarterEnd(AddMonths(today(),-9))>=date(lease_from),if(QuarterEnd(AddMonths(today(),-9))<=date(lease_to),'y','n'),'n'))