Search not working in Analysis

[Coolavin]

The search feature isn't working. 

When I click the magnifying glass button on the status column and then select Active.

It drops the Active totals down to 818,774 it should be 1,024,959.

It works correctly when I select the Inactive. Hopefully someone can help me figure this out. 

[Coolavin]

An account that is active is:

when it's CredentialExpireDate greater than or equal to today's date or it's null, blank or empty in the CredentialExpireDate cell. 

"- you remove the non null CredentialExpireDate (-<[CredentialExpireDate]={'*'}> )"

Yes, I remove the non nulls so that all I am left with is the nulls. then I add the records that have CredentialExpireDate  as greater than or equal to today's date. 

[Coolavin]

When I use this expression:

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

It makes all the records that are blank or null in the CredentialExpireDate field to have 'Inactive' in the Status column instead of 'Active' now.  They need to have Active in the Status column.  However, even when I try the search on 'Inactive' it still leaves them out.

[lorenzoconforti]

But you also remove the blank/empty ones

I would do it differently and define it as all the records less the ones where CredentialExpireDate is lower than today 

In your KPI:

=Sum(aggr(COUNT( {$-<[CredentialExpireDate] = {'<$(=Date(Today()))'}>}[CredentialMeasure]),[CredentialMeasure]))

In your dimension:

=If([CredentialExpireDate] < Today(), 'Inactive', 'Active')

Please note that when CredentialExpireDate is blank or null the conditions will evaluate to False and determine the status as Active; if date is 0 that's a different story of course

[Coolavin]

When I use this expression in the Status column:

=If([CredentialExpireDate] < Today(), 'Inactive', 'Active')

I get this:

The expression doesn't do anything for those records that are blank/null in 'CredentialExpireDate'.

That's why I had to use this expression:

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

The last 'IF Statement' of it says if the records don't meet the first two condition then,  if the count of "CredentialExpireDate" = 0 (it can equal anything really) make it 'Inactive', it will never equal anything so it will never end up being 'Inactive' because there is nothing there in 'CredentialExpireDate' so it will end up making those records 'Active' which it does. 

And the numbers do add up. I have exported it and all the records that have 'Active' in Status equal the Active KPI number. It's just the Search button that messes things up. 

[lorenzoconforti]

Strange this is not working as it is a part of your dimension already (it's the first if statement so it should work as a dimension):

=If([CredentialExpireDate] < Today(), 'Inactive', 'Active')

let's try to add the other conditions (blank and null) in the if statement without the aggregation (maybe that's causing the issue)

=If( ([CredentialExpireDate] < Today()) and (([CredentialExpireDate]  <> '') and ( not( IsNull(([CredentialExpireDate] ) )  )  ), 'Inactive', 'Active')

[Coolavin]

I tried the expression you gave:

=If( ([CredentialExpireDate] < Today()) and (([CredentialExpireDate]  <> '') and ( not( IsNull(([CredentialExpireDate] ) )  )  ), 'Inactive', 'Active')

And it gives the same results. It doesn't want to do anything directly with the records that are blank/null in 'CredentialExpireDate'. 

The thing that is so frustrating is that I can create KPIs to pull the correct numbers and I can get the Status dimension column in the table updated correctly and export it and check the numbers of the data from the export and they all add up to the KPI numbers. However, the search magnifying glass button messes all the numbers up, for some reason it wants to ignore any record that is blank/null in the 'CredentialExpireDate' even though it should only be searching in the Status column which is all filled out. 

As you can see in the screen shot below I have the total certifications, Active credentials(which includes the forever credentials), and the Inactive credentials. 

Below is when I select Active in the search in the Status column. You can see how it drops out the inactives like it should but it also drops out the forever credentials which it shouldn't

Below you can see when I select Inactive in the search in the Status column. Everything works as expected. 

The numbers are all correct until I try to use the search for Active in the Status column.

The numbers break out as:

-Total Credentials: 3,860,383

-Active Credentials: 806,476

-Forever Credentials: 205,631

-Inactive Credentials: 2,848,276

The Active Credentials should include the Forever Credentials. I just separated them to help try to figure out what is going on. 

[lorenzoconforti]

No chance you can post/send me the file after removing any sensitive information?

Maybe you could just mark in your loading script the forever credentials (you don't really need to compare those values to today's date as it would be irrelevant anyway); the if statement should then be more straightforward

[Coolavin]

Send you the export file?

[lorenzoconforti]

The dashboard itself

[Coolavin]

Let me see what I can do. I am not 100% on how to send you the dashboard.