Work around for filters and empty fields?

[Coolavin]

Hello, I am running into an issue with filtering the status column in the screenshot below. 

Ther are 1,024,959 in Active. However when I select Active from the Status column the number of Active changes to 818,774 see the screenshot below. 

The status column is based on "CredentialExpireDate" being less than today's date (inactive) or being greater than or equal to today's date (active). However, some of the "CredentialExpireDate" fields are empty, they are not null or blank they are empty and I know with Qlik that means they don't exist and can't be evaluated. 

I found a way around that to populate the status column my using the expression below:

=If([CredentialExpireDate] >= Today(), 'Active',If([CredentialExpireDate] < Today(), 'Inactive', If(aggr(alt(count(CredentialExpireDate),0),primarykeyfieldofothertable)=0,'Inactive', 'Active')))

This expression says if the "CredentialExpireDate" is greater than or equal to today's date then put "Active" in the status column, if "CredentialExpireDate" is less than today's date then put "Inactive" in the status column, everything else doesn't have an "CredentialExpireDate" because the feild is empty so it puts "Active" in the status column. 

This works great for updating the text in the status column, however when we try to filter on that column it seems to only use the first two parts of the expression and not the last part that says if there is nothing in the "CredentialExpireDate" than put "Active" 

The totals at the top for active and inactive just have these expressions:

=sum(if(CredentialExpireDate < Today() ,0,1)) for Active

or

=sum(if(CredentialExpireDate < Today() ,1,0)) for Inactive

and it is able to figure out the totals correctly. 

This there a way to just look at the text of the status column to base the filter off of? or is there something different that should be done to populate the status column?

[TimvB]

I would recommend solving this problem in the script by creating an extra dimension. This can be done as follows:

NewTableName:

Noconcatenate Load

*,

If([CredentialExpireDate] >= Today() OR IsNull([CredentialExpireDate] ), 'Active', 'Inactive') AS [CredentialStatus]

Resident TableName;

Drop Table TableName;

This way you create an extra field that can be applied to a filter pane directly. Use set analysis for the calculations, for example:

=sum(if(CredentialExpireDate < Today() ,0,1)) for Active

needs to change to:

=count({<[CredentialStatus] = {'Active'}>} [CredentialStatus] )

Also, the script is much faster than the front end, so this will speed-up your app.

Hope it helps!