Count only once (without duplicates)

[#1]

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 ?

[Anonymous]

Try count(DISTINCT Defect ID)

[Gysbert_Wassenaar]

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

[Anonymous]

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

[Anonymous]

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

[#6]

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.

[Gysbert_Wassenaar]

Then use year as dimension instead on month.

[jonathandienst]

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,

[jonathandienst]

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

[Anonymous]

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