Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

How to calculate a "1 of a kind" ratio?

Hello, I'm looking for a method of calculating & displaying a '1 of a kind' ratio in a text object. The data in a table would look something like this:

Item     Qty     1s
10       3
bcd         5       0
cde         2       0
def          1       1
efg          1       1
fgh          1       1

Qty is the count of Item and 1s is a calculated expression if(column(1)<2,1,0). The ratio I would like to display in a test object is 3/10 i.e. 0.3. I don't want or need column 1s. I'm only using it to illustrate what I mean by '1 of a kind'. I'm pretty sure that this should be able to be done using the aggr function but I'm new to QlikView and can't seem to get it to work. Attached is a sample file. Any suggestions would be greatly appreciated. Thanx.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

How to calculate a "1 of a kind" ratio?

Hi,

yes, you are right in using the aggr() function, try this:

=count(aggr(if(Count(REPORTED_DEFECT_ID)=1,REPORTED_DEFECT_ID),SHORT_REJECTION))

  / Count(REPORTED_DEFECT_ID)

Hope this helps,

Stefan

2 Replies
MVP
MVP

How to calculate a "1 of a kind" ratio?

Hi,

yes, you are right in using the aggr() function, try this:

=count(aggr(if(Count(REPORTED_DEFECT_ID)=1,REPORTED_DEFECT_ID),SHORT_REJECTION))

  / Count(REPORTED_DEFECT_ID)

Hope this helps,

Stefan

Not applicable

Re: How to calculate a "1 of a kind" ratio?

Perfect - thank you very much Stefan