Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count only once (without duplicates)

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

    

Defect IDCreatedModifiedNumber of Defects
xxxxx11.1.20151.2.2015
xxxxx210.1.201512.1.20153
xxxxx529.1.20153.5.2015
xxxx184.4.20154.4.20151
xxx105 count this28.12.20158.1.20161
xxx105  don’t Count this28.12.20158.1.2016
xxx20313.1.201616.1.20161

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.

noofdefects.JPG

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 ?

20 Replies
Not applicable
Author

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi,

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

svinnakota
Creator
Creator

Hi James,

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

Not applicable
Author

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

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.