Alternate expression help

[stevelord]

Hi Community, I have one expression that says 'who completed activities a and b' and another expression that says 'who completed activities a, b, and c' that looked like they were doing fine for a long time.  The 'who completed a and b' was coming out a bit bigger than 'who completed a, b, and c' total as they should for a variety of clients.  Then I used this dashboard on a particular client for the first time, and the numbers came out the wrong way around.  Below are the two expressions.  Help with enhancing the expressions or ideas about what in the data structure could be the problem would be great.  Thanks!

1> Who completed HA and Biometric - I select HA from field HA_Incent in the HA State and Biometric from field Biometric_Incent in the Biometric State

=count(Distinct

{[AR Biometric]*[AR HA]}

UserId)

Answer for client 123 is 1505

Answer for client 456 is 2242

2> Who completed HA and Biometric and at least 1 other activity - selections from above, and I select activities from field Incentive in the Incentive State

[stevelord]

Sorry, browser is doing something weird and stopped me from typing more in the original post.  Second expression is:

=count(Distinct

{[AR HA]*[AR Biometric]*[AR Incentive]}

if(

Aggr(TextCount(Distinct Incentives),UserId)>=1,

UserId))

Alternate form of second expression that seems to have the same result is:

=count(Distinct

{[AR HA]*[AR Biometric]*[AR Incentive]}

if(

Num(RangeSum(Aggr(TextCount(Distinct Incentives),UserId)))>=1,

UserId))

Answer for client 123 is 960 - goes down like it should

Answer for client 456 is 2389 - goes up...

[stevelord · Accepted solution]

The root cause remains a mystery, but I went into the source data to confirm which of the conflicting answers was correct then focused on the first expression in the first post which was the one in disagreement with the raw data.

On a hunch, I tried something that should not have mattered at all, and switched it from count distinct userid to count distinct employee id, and the number trued up with the raw data and other expressions.  I went through all of the other expressions to update user id to employee id for consistency and none of their answers changed whatsoever, except the couple that had the problem expression as a component of their larger expression.

So.. the answer is nfc.  I can't pin it on our data structure.  The user ids are pure numbers with no leading 0s, the employee ids can be any string, so I'd expect parsing anomalies with external ids more than user ids...  my other 2-state and 3-state expressions all gave correct counts with user ids even...  None of the other clients' counts, which were correct in the first place, were otherwise affected by this fix for this client... Even for this client the invisible anomaly was only in 2013 data and not their 2014 data... just.. nfc...