Please help in distinct count

[#1]

I have a data like this

    BU, Supplier

    A, a

    A, b

    A, c

    B, a

    B, d

    B, e

    B, f

    C, d

    C, a

    C, f

I want to show the number of common supplier in BUs

and I want to achieve this.

Is it possible in Qlikview?

BU	A	B	C
A
B	1
C	1	2

[Gysbert_Wassenaar]

See attached example.

[swuehl · Accepted solution]

Here is another option.

[Gysbert_Wassenaar]

Follows? That's a new one for me. I can't seem to find it in the help file either. Can you explain to me how it works?

[jonathandienst]

Its new to me too, so I looked in the reference manual, and it is indeed there (follows and precedes). They are relational operators:

precedes  ASCII less than

Unlike the < operator no attempt is made to make a numeric interpretation

of the argument values before the comparison. The operation

returns true if the value to the left of the operator has a text

representation which, in ASCII comparison, comes before the text

representation of the value on the right.

follows  ASCII greater than

Unlike the > operator no attempt is made to make a numeric interpretation

of the argument values before the comparison. The operation

returns true if the value to the left of the operator has a text

representation which, in ASCII comparison, comes after the text

representation of the value on the right.

[#6]

Thanks a lot.

swuehl‌ and gwassenaar‌ both seems right.

[Gysbert_Wassenaar]

Thanks! That certainly helps me understanding how Stefan's solution works. It seems very dependent on the sorting though, so it might not work in many other cases.

[swuehl]

I haven't intended to confuse anybody.

I think the main difference is within the script / data model. A simple Count(Distinct Supplier) should return correct values in the chart.

The additional if() Statement with the Follows Operator is just cosmetics, to resemble the look & feel of the OP's chart, showing only lower half of values.

Maybe I just shouldn't have used it in my example (and Gysbert is right, it may not work out under all (sort) conditions),

I just want to use these operators once in a while.

[Gysbert_Wassenaar]

Oh, don't worry about confusing us. I learned something new today, so thank you for that!.