topic Re: Blank Filter with Conditional Statement in QlikView

Blank Filter with Conditional Statement

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a filter with a blank

I have tried different methods of filtering in stages: one filtering dates from 2014 to 2017 and the eliminating the blanks:

if(year([first_paymnt_date]) =< 2014,'',year([first_paymnt_date]) &(first_paymnt_date <>' ',(first_paymnt_date)))

if(year([first_paymnt_date]<>' ', year(first_paymnt_date)) &year(first_paymnt_date)=< 2013))

No success I was wondering if anyone might have a suggestion

Re: Blank Filter with Conditional Statement

vishsaggi — Thu, 28 Sep 2017 23:01:22 GMT

Can you explain a little more here what exactly you are trying to achieve.?

if(year([first_paymnt_date]) =< 2014,'',year([first_paymnt_date]) &(first_paymnt_date <>' ',(first_paymnt_date)))

if(year([first_paymnt_date]<>' ', year(first_paymnt_date)) &year(first_paymnt_date)=< 2013))

This expression doesn't seem to be right. Can you explain a little. ?

Re: Blank Filter with Conditional Statement

Anonymous — Fri, 29 Sep 2017 14:57:01 GMT

Correction:

if(year([first_paymnt_date]) =< 2013,'',year([first_paymnt_date]) &(first_paymnt_date <>' ',(first_paymnt_date)))

if(year([first_paymnt_date]<>' ', year(first_paymnt_date)) &year(first_paymnt_date)=< 2013))

The steps are: 1) select on data from 2013 on; 2) eliminate blanks or NULLs which are shown in the filter. Maybe I should break the code into 2 statements

Thanks

Re: Blank Filter with Conditional Statement

vishsaggi — Fri, 29 Sep 2017 17:11:04 GMT

Try this may be

IF(IF(Len(Trim(first_paymnt_date)) > 0, Year(first_paymnt_date)) >= 2013, Year(first_paymnt_date))

Re: Blank Filter with Conditional Statement

Anonymous — Fri, 29 Sep 2017 17:32:05 GMT

No quite it because I am looking at blanks that I want to eliminate, but thanks, I think I got how to embed the two if statements. Something like this:

if(if(Year(first_paymnt_date)<>'',Year(first_paymnt_date)) >= 2013, Year(first_paymnt_date)) Thanks for your assistance

Re: Blank Filter with Conditional Statement

vishsaggi — Fri, 29 Sep 2017 17:42:08 GMT

Len(Trim()) would eliminate the blanks actually did they not?

Re: Blank Filter with Conditional Statement

Anonymous — Tue, 03 Oct 2017 17:43:17 GMT

Thanks Len(Trim()) works, but now need to display year and month. Any idea for example what to start with 2014-01

Re: Blank Filter with Conditional Statement

Anonymous — Tue, 03 Oct 2017 19:01:28 GMT

Found my solution so to share with others:

IF(IF(Len(Trim(first_paymnt_date.autoCalendar.YearMonth)) > 0, Year(first_paymnt_date.autoCalendar.YearMonth)) <= 2013,'',year([first_paymnt_date.autoCalendar.YearMonth])&'-'&Month([first_paymnt_date.autoCalendar.YearMonth]))

Re: Blank Filter with Conditional Statement

vishsaggi — Tue, 03 Oct 2017 19:13:40 GMT

Glad you figured it out. Can you close the thread marking Correct/helpful responses accordingly.