topic Count Distinct IF - Straight Table in QlikView

Count Distinct IF - Straight Table

Mon, 12 Jul 2010 09:12:45 GMT

Has anyone en-counted the same problem as me here goes.

We are in a call center environment one of the things we measure is the no of accounts that an agent may work for a given time period.

I have a straight table which has an expression that does the ffg

"count(distinct(if(Status = 'Action', [Account Code])))"

The result set that come of this is as follows.

Date	Accs Touched
	17,284
2010/07/01	3,211
2010/07/05	4,235
2010/07/06	3,084
2010/07/07	4,012
2010/07/10	1,032
2010/07/02	3,461
2010/07/09	2,428
2010/07/08	3,008

Total as per Qlikview Totaling " Expression Total" : 17284 which is correct

However if you add up each of the days data it totals to 24471, we have established that it is not distinct counting at an un-grouped level.

any help will be appreciated

thanks

Shaun

Count Distinct IF - Straight Table

johnw — Tue, 13 Jul 2010 02:01:18 GMT

I'm not sure I'm understanding. For each Date, QlikView will count the distinct 'Action' accounts for that day. Assuming an 'Action' account can be associated with more than one Date, then it will be counted under more than one date. even though it is only counted once per date. But the total line counts distinct 'Action' accounts across the entire date range, so would be lower.

Count Distinct IF - Straight Table

Tue, 13 Jul 2010 08:52:45 GMT

Hi John

1. An account can have many status's associated with it (i.e 'Action')

2. An account can be 'actioned' (commented on ) on many days.

3. An account can be ('commented on by multiple agents on a day/s.

4 An account can have different comments or same comments on an account by different users or same users on the same day or different day.

I am looking for each day distinct accounts that were commented on by users

I hope I haven't confused you even more.

Shaun

Count Distinct IF - Straight Table

johnw — Tue, 13 Jul 2010 17:36:37 GMT

I don't THINK you've confused me even more. I think you've confirmed for me exactly why your data looks the way it does. Let me take a very simple example:

Date, Account Code, Status, Agent
2010/07/01, 1, Action, Bob
2010/07/01, 2, Action, Bob
2010/07/02, 1, Action, Bob
2010/07/02, 3, Action, Bob

Here's the chart you'll get:

Date Accs Touched
Total 3
2010/07/01 2
2010/07/02 2

All three rows in the chart are correct, even though 2 + 2 does not equal 3. Bob touched a total of three accounts. On July 1, Bob touched two accounts. On July 2, Bob touched two accounts. If that isn't what you want to see, what DO you want to see? THAT would be my confusion.

But perhaps I've misunderstood.