Sum and count are not adding up.

[Coolavin]

I have a sum and a count that should be adding up to the same. but the sum has about 4,000 more records than the count.  See below:

=count( if({<[CredentialExpireDate] = {'<$(=Date(Today()))'}>}CredentialMeasure))

=Sum( if([CredentialExpireDate] < Date(Today()),1,0))

They both look at CredentialExpireDate and count or sum based on if CredentialExpireDate is less than today's date. I kow that the count is correct.

Is there a better way to do this?

[Kushal_Chawda]

What exactly your question is? If count expression is correct then why you are not using it?

Also, count expression seems to be wrong. it should be like below

=count( {<[CredentialExpireDate] = {"<$(=Date(Today()))"}>}CredentialMeasure)

[TimvB]

You should create an extra dimension to tag the 'Active' and 'Inactive' Credentials. Then use 'set analysis' to count or sum the total. See my previous comment:

https://community.qlik.com/t5/New-to-Qlik-Sense/Work-around-for-filters-and-empty-fields/m-p/1662699...

[Coolavin]

Question is: why don't the sum and the count match? 

[rwunderlich]

I'm assuming you mis-typed the Count() expression, as it is invalid as you posted it. Assuming it's as @Kushal_Chawda corrected it, a difference would be that if() would include null CredentialDate, the set analysis would not. 

-Rob

[Kushal_Chawda]

There is difference between both the expression

Count expression specifically counts the CredentialMeasure wherever condition is met.

Catch here is, when CredentialMeasure don't have any value(Null) it will not be counted although condition is met while Sum expression will count it because in sum expression you are just flagging the 1 wherever condition is met. Hence, sum expression counting all the rows wherever condition is met whereas count expression just conts the rows where condition is met and CredentialMeasure is not null.