Issue with Counting Distinct

[#1]

Hello,

I am trying to count distinct 'Call: Call ID' using set analysis. I have include the script I am using below. However, when I use this method, I am missing about 30 records from the data set. I have confirmed that there should be 241 records returned but I am only getting 210. Is there something wrong with my script? Any other suggestions as I am stuck...

Thanks.

Count(distinct{$<[Liason Type]-={'FME','AML'},[Product: Description]={'Apples'},Objective-={'Presentations'}>} [Call: Call ID])

[#2]

I posted de-identified data set that will be similar to the data I am using. Hope this helps.

[sunny_talwar]

From the new sample, I am seeing a count of 3827. Is that right?

[swuehl]

I still assume there may be an issue with NULL in the fields you filter (as described in Henric's blog post).

If I interpret empty field values as NULL, your expression returns 3813, if I don't, 3827.

[#5]

I was focusing on the Month of August to check numbers in order to get them more manageable. For August the total for interactions of 'Face to Face' and 'Phone' should be 238. I am getting 210.

[#6]

For the August data there are null values in the 'Parent Call' field so maybe this is the issue. How should I try this in my script to test?

[swuehl]

I would rather look at the fields you modify in the set expression, i.e. Objective for example, which seems to show NULL for some records, too.

Maybe something along

=Count(distinct{$<[Call: Call ID]=e({<Objective={'Presentations'}>}),[Liaison Type] -={'FME','AML'},[Product: Description]={'Apples'}>} [Call: Call ID])

though I can't reproduce your exact numbers, also using your original expression.

[sunny_talwar]

Using the CSV you have shared and filtering to the selections you have mentioned about and the selections you have in your set analysis, I am only able to see 210 for July (not August). I am attaching the filtered csv file for you to guide us on what other rows did we miss to add up?

Filters

1) Datetime = July 2016

2) Interaction Type = Face to Face and Phone

3) Objective = Everything except presentation

4) Product: Decription = Apples

5) Liason Type = Everything except AML and FME

If you want to count August, then I am getting 259 which seems higher then both your numbers. Not sure if that is the right month to look at, but I am attaching a CSV file for that as well.

I guess if you can point us how you are counting the number of rows in the csv file we might be able to locate the issue. Because right now, it seems your expression is doing the calculation rather correctly as it suppose to do. But may be you are looking for something slightly different.

Best,

Sunny

[#9]

Thanks. I can duplicate your numbers for July in both the deidentified data set and my original data set. However, for the August, I still only get 239 rows. I am using the same filter criteria as above but using August instead of July as the month.

[sunny_talwar]

In the Excel file or in the QlikView application?

[#11]

In the Excel file.