Creating a Yes/No filter from a formula

[JimAdlhochVPH]

We have a system where medical procedures have a Start date/time and an End date/time. Since the End date/time is not required, there are instances where it is not populated, showing as blanks.

Our existing app has a simple expression metric showing the number of blanks in that field:

count(OrderNumber)-count(EndDateTime)

It is counting the total number of orders for the procedure, and subtracting the number of procedures with an End date/time, resulting in the number of fields which are blank.

I want to create a filter with an expression using the formula, so that the app user can include or exclude the blanks. The filter would have two choices, Yes or No, to include or exclude the blanks.

I'm fairly new to Qlik Sense and thought one of the gurus out there might have a simple solution before I start scouring for syntax in building the expression.

Thanks in advance for the help!

[rwunderlich]

The best approach is to create a new field in the load script:

if(len(EndDateTime) > 0 , 'No', 'Yes') as [Has End Date]

and then use the [Has End Date] in a filter.  

Second best would be to create a filter using an expression like:

=aggr(only({1}if(Len(EndDateTime) > 0,'Yes','No')), OrderNumber)

Selecting Yes or No will select the OrderNumbers associated with that value. The current selections will show "n of n" values which is not as nice as solution #1. 

-Rob
http://masterssummit.com
http://qlikcookbook.com
http://www.easyqlik.com

[JimAdlhochVPH]

Rob -- thank you for the quick response back. Because of the way that Qlik handles "blank" values, we found populating the blanks with NULL worked with code very similar to yours.

=aggr(if(isnull(EndDateTime),'N','Y'),EndDateTime)

Appreciate your help! You ROCK!