Please help with getting answer from data

[cthomas2]

Hello,

I am curious how I can get an answer based on a group of data and then count the "index" that represents that group... here is an example very simplified table:

Index	Value
1	a
1	a
1	b
2	a
2	a
2	a
3	a
3	b
4	a
5	b

Effectively, I am trying to count the number of indices that distinctly have a value of 'a'. 1 would not be counted because the 3rd record contains a b, 2 would be counted because it only contains the value a (but is only counted once), 3 would not, 4 would count and 5 would not... so when I run the expression on the above table, the resulting count would be 2.

I actually have another level to add to this, but I think if I can understand how to accomplish my goal on just this set of data, I could extract what I need on the larger set...

Any help on this one? Thanks in advance!

[jwjackso]

Using the AGGR function, if the number of distinct counts is 1, return 1, otherwise 0.  Suppress zeroes rows.

[ehilsinger]

I created a chart with the Index as a dimension and this as the expression.

= Count ({$<[Index] -= P({<[Value] -= {'a'}>})>}  Distinct [Value])

Will this get you started?

[maxgro]

try

- (only(TOTAL <Index> Value) = 'a')

[cthomas2]

Thanks for the response, however 3 is incorrect as the distinct values needs to be specifically 'a'.

[cthomas2]

This result looks correct, however I just get a memory allocation error when I try putting it in and changing Index & Value for the actual field names...

[MarcoWedel · Accepted solution]

Hi,

another expression might be:

hope this helps

regards

Marco

[qlikviewwizard]

Try this in a text object.

=count(distinct aggr((only(TOTAL <Index> Value) = 'a'),Index))

[cthomas2]

This is it! I was also able to integrate it into the "next level" I indicated into the original post and it matches up with all the counts I've already manually calculated.

It did not occur to me to just try to SUM the -1's, I kept trying to convert them into something else and then count them... such a simple change!

Thanks everyone for trying to help me with this... I learned a lot just from trying everyone's suggestions!