topic Re: Count only once (without duplicates) in QlikView

Count only once (without duplicates)

Thu, 10 Nov 2016 13:04:46 GMT

I have a set of qvds for various years like below:

Defect ID	Created	Modified	Number of Defects
xxxxx1	1.1.2015	1.2.2015
xxxxx2	10.1.2015	12.1.2015	3
xxxxx5	29.1.2015	3.5.2015
xxxx18	4.4.2015	4.4.2015	1
xxx105 count this	28.12.2015	8.1.2016	1
xxx105 don’t Count this	28.12.2015	8.1.2016
xxx203	13.1.2016	16.1.2016	1

Here, Some of the defects may be entered more than once in the table(See Defect ID) because they may be created in 2015 and still open or modified/closed in 2016. Number of defects have to be taken for counting according to the “Created” date.

For example if issue was created in the earlier year(e,g.2015), then you can skip it in this year’s data(2016).

My dashboard table looks like below.

How do i re-write the expression to show the count of defects in the above table for each Year month without counting the same defect code in more than once ?

Re: Count only once (without duplicates)

Anonymous — Thu, 10 Nov 2016 13:11:36 GMT

Try count(DISTINCT Defect ID)

Re: Count only once (without duplicates)

Gysbert_Wassenaar — Thu, 10 Nov 2016 13:13:32 GMT

count(distinct [Defect ID]) will count the number of unique Defect ID values.

Re: Count only once (without duplicates)

Anonymous — Thu, 10 Nov 2016 13:14:05 GMT

=If(Aggr(NODISTINCT count(Created), Modified) > 1, count(Number of Defects))

Re: Count only once (without duplicates)

Anonymous — Thu, 10 Nov 2016 13:15:15 GMT

=If(Aggr(NODISTINCT count(Created), Modified) > 1, count(Defect ID))

Re: Count only once (without duplicates)

Thu, 10 Nov 2016 13:44:24 GMT

Yes, This is my current expression. But it includes the count of same 'DefectID' that appears in more than one year.

So it should be modified to count DefectID's without repetitive.

Re: Count only once (without duplicates)

Gysbert_Wassenaar — Thu, 10 Nov 2016 13:48:03 GMT

Then use year as dimension instead on month.

Re: Count only once (without duplicates)

jonathandienst — Fri, 11 Nov 2016 06:12:04 GMT

If you want to count the first appearances of each defect ID, then you need a little more. Like

=Count(Aggr(DISTINCT If(Created = Min(TOTAL <[Defect ID]> Created), [Defect ID]), [Defect ID], Month, Year))

(based on your sample data above)

Defect ID, Created, Modified, Number of Defects

xxxxx1, 1.1.2015, 1.2.2015,

xxxxx2, 10.1.2015, 12.1.2015, 3

xxxxx5, 29.1.2015, 3.5.2015,

xxxx18, 4.4.2015, 4.4.2015, 1

xxx105, 28.12.2015, 8.1.2016, 1

xxx105, 28.12.2016, 8.1.2016,

xxx203, 13.1.2016, 16.1.2016, 1

With a more complete set of test cases:

Defect ID, Created, Modified, Number of Defects

xxxxx1, 1.1.2015, 1.2.2015,

xxxxx2, 10.1.2015, 12.1.2015, 3

xxxxx5, 29.1.2015, 3.5.2015,

xxxx18, 4.4.2015, 4.4.2015, 1

xxx105, 28.12.2015, 8.1.2016, 1

xxx105, 28.12.2016, 8.1.2016,

xxx203, 13.1.2016, 16.1.2016, 1

xxxxx6, 20.2.2015, 3.5.2015,

xxxxx6, 20.4.2016, 3.5.2015,

xxxxx7, 20.3.2015, 3.5.2015,

xxxxx7, 20.3.2015, 3.8.2015,

xxxxx8, 20.8.2015, 3.5.2016,

xxxxx8, 20.10.2015, 3.10.2015,

Re: Count only once (without duplicates)

jonathandienst — Fri, 11 Nov 2016 06:18:22 GMT

Or you could flag the first appearances in the load script - either do a sorted load with an Exists to detect the first instance

LOAD [Defect ID],

If(Exists([Defect ID]), 0, 1) as FlagFirst,

Created,

...

FROM ...

Order by [Defect ID], Created;

, or join with

Join(Data)

LOAD [Defect ID],

Min(Created) as Created,

1 as FlagFirst

Resident Data

Group By [Defext ID];

Then you can count the using :

=Count({<FlagFirst = {1}>} Distinct [Defect ID]);

Re: Count only once (without duplicates)

Anonymous — Fri, 11 Nov 2016 06:57:56 GMT

=If(Aggr(DISTINCT count(Created), Modified) > 1, count(Defect ID)

Re: Count only once (without duplicates)

Fri, 11 Nov 2016 10:22:39 GMT

I was not able to implement your previous idea, but with this I am getting NULL for count

Re: Count only once (without duplicates)

Fri, 11 Nov 2016 10:34:37 GMT

I am not sure what Min(TOTAL <[Defect ID]> Created) this does in your expression, can you please post the sample app you created.

FYI : there is no Month , Year column in my table , so I replaced it with Month(Created) , Year(Created) in your expression and I hope it does not make any difference.

Re: Count only once (without duplicates)

jonathandienst — Tue, 15 Nov 2016 05:33:24 GMT

No, you cannot use expressions for the dimensions in an Aggr(). Use the underlying field:

Count(Aggr(DISTINCT If(Created = Min(TOTAL <[Defect ID]> Created), [Defect ID]), [Defect ID], Created))

Re: Count only once (without duplicates)

jonathandienst — Tue, 15 Nov 2016 05:35:19 GMT

TOTAL <[Defect ID]> is a partial total by Defect ID

Re: Count only once (without duplicates)

Thu, 01 Dec 2016 05:01:27 GMT

This expression counts the defects and shows them in the first month only. It does not show the month wise defect. I have attached the sample app. If I put general count (commented in the expression tab) it shows same count value for all the months, Please help in correcting the expression to show month wise defect count without counting the same defect number if it appears in the consecutive years.

-JB !

Re: Count only once (without duplicates)

jonathandienst — Thu, 01 Dec 2016 05:17:45 GMT

Your calender table (Cal) has no association with your fact table, so you cannot use a field here as a dimension for an expression from the fact table.

And there are no duplicate NCF's in your sample data, so its not much good as a test set.

Re: Count only once (without duplicates)

Thu, 01 Dec 2016 05:57:46 GMT

Hi,

I want you to quickly refer this https://community.qlik.com/thread/239814 . There is no link in the table here too.

Re: Count only once (without duplicates)

svinnakota — Thu, 01 Dec 2016 06:28:26 GMT

Hi James,

Please find the attached app and please let me know if it works or not.

Re: Count only once (without duplicates)

Thu, 01 Dec 2016 07:18:41 GMT

I think your expression counts the defects by Distinct Created date. But the logic to be implemented is distinct defect codes... Ignore the first inline data... Please find the actual app attached in comments...

-JB

Re: Count only once (without duplicates)

svinnakota — Thu, 01 Dec 2016 07:45:56 GMT

Please re-write the expression as

count(distinct{< Created>} [Defect ID])

It gives the cound of distinct id's based on the date.

If this does not work please attach the app once again. I am not able to find the actual app which you are referring to.