Getting count

[help4qv123]

Hi,

I have a requirement . attached is the sample app. Plz see the sample file.

There are 2 columns . Number and  Val.

In Numbers column,

Number 3 and 5 have a single value associated.

ie.., Number 3 has 'abc' and number 5 has 'abc...remaining numbers have more than 1 different values

I need count of Numbers which have Val 1 and count of value which have Valu more than 1

[sunny_talwar]

Try this:

=Count({<Number = {"=Count(Val) = 1"}>}Number) for Numbers with only 1 value

=Count({<Number = {"=Count(Val) > 1"}>}Number) for Numbers with more than 1 value

[help4qv123]

when i use

=Count({<Number = {"=Count(Val) = 1"}>}Number) for Numbers with only 1 value

it should get 2. Because Number 3 and 5 has single value associated. ,

[sunny_talwar]

Right, is it not giving you that?? I am working on a personal edition of QlikView so can't open the document you shared.

[jonathandienst · Accepted solution]

This works:

=Sum(Aggr(If(Count(Distinct Val) = 1, 1, 0), Number)

[help4qv123]

oh...i will share u an excel . You can load it . and take the columns in list box and check.

[sunny_talwar]

Try these:

=Count(DISTINCT{<Number = {"=Aggr(Count(DISTINCT Val), Number) = 1"}>}Number) and

=Count(DISTINCT{<Number = {"=Aggr(Count(DISTINCT Val), Number) > 1"}>}Number)

[antoniotiman]

Count({<Number={"=Count(DISTINCT Val)=1"}>} DISTINCT Number)

[help4qv123]

Thanks jonathan...

Its Working....