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 ?

[#2]

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

[#3]

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.

[jonathandienst]

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

[jonathandienst]

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

[#6]

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 !

[jonathandienst]

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.

[#8]

Hi,

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

[svinnakota]

Hi James,

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

[#10]

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

[svinnakota]

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.