topic Re: Issue with Counting Distinct in App Development

Issue with Counting Distinct

Wed, 22 Dec 2021 15:42:54 GMT

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])

Re: Issue with Counting Distinct

sunny_talwar — Fri, 16 Sep 2016 16:42:25 GMT

Would you be able to share a sample to check this out? Expression in itself doesn't look wrong.

Re: Issue with Counting Distinct

Fri, 16 Sep 2016 16:59:29 GMT

Sure. Would you like a sample of the data set?

Re: Issue with Counting Distinct

Fri, 16 Sep 2016 17:14:37 GMT

Sure. Would you like a sample of the data I am using or other?

Re: Issue with Counting Distinct

sunny_talwar — Fri, 16 Sep 2016 17:43:27 GMT

Qvw or Excel data will work. If you are providing Excel, please let us know the dimensions you have used

Re: Issue with Counting Distinct

swuehl — Fri, 16 Sep 2016 17:49:57 GMT

Do you have NULLs in your fields you are trying to filter?

And you don't have active selections in your model?

Also have a look at

Excluding values in Set Analysis

Re: Issue with Counting Distinct

Fri, 16 Sep 2016 18:13:29 GMT

There may be a few NULLs in the dataset but I am not trying to filter these.

I am trying to build a line graph that shows number of interactions per month over a period of time. I just want the graph and do not need/want selection filters on this particular sheet.

I will check out the link provided.

Thanks.

Re: Issue with Counting Distinct

sunny_talwar — Fri, 16 Sep 2016 19:12:26 GMT

The attached doesn't have a field called Liason Type, don't know if that is important or not but you have used that in your expression. Without using that, I am getting a distinct count of 503.

Used this expression:

=Count({$<[Product: Description]={'Apples'}, Objective-={'Presentations'}>} DISTINCT [Call: Call ID])

Re: Issue with Counting Distinct

Fri, 16 Sep 2016 19:37:12 GMT

Thanks! Yes, I get this number when just counting in Excel and removing duplicates. This tells me the script is working in this case. However, then I apply the script to my other data set, I still only get 210 records and there should be 238 (I counted manually). This is very strange...

Re: Issue with Counting Distinct

sunny_talwar — Fri, 16 Sep 2016 19:50:31 GMT

What is that extra part? Can we have that too to check?

Re: Issue with Counting Distinct

Fri, 16 Sep 2016 21:00:46 GMT

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

Re: Issue with Counting Distinct

sunny_talwar — Fri, 16 Sep 2016 21:13:51 GMT

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

Re: Issue with Counting Distinct

swuehl — Fri, 16 Sep 2016 21:14:48 GMT

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.

Re: Issue with Counting Distinct

Fri, 16 Sep 2016 21:36:23 GMT

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.

Re: Issue with Counting Distinct

Fri, 16 Sep 2016 21:38:35 GMT

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?

Re: Issue with Counting Distinct

swuehl — Fri, 16 Sep 2016 21:57:28 GMT

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.

Re: Issue with Counting Distinct

sunny_talwar — Sun, 18 Sep 2016 17:15:41 GMT

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

Re: Issue with Counting Distinct

Mon, 19 Sep 2016 13:22:38 GMT

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.

Re: Issue with Counting Distinct

sunny_talwar — Mon, 19 Sep 2016 23:38:37 GMT

In the Excel file or in the QlikView application?

Re: Issue with Counting Distinct

Tue, 20 Sep 2016 04:39:32 GMT

In the Excel file.