how to solve this one?

[#1]

Hi everybody,

i don't know if it's caused by my free version of Qlikview or by my personal knowledge weakness (in QV logics); however this is my problem:

I have many Buildings. Every Building has many elements. Every element belongs to (only) one category (out of many). Every Element has received a rating.

For example:

BUILDING	ELEMENT	CATEGORY	RATING
A	1	X	-3
A	2	X	0
A	3	Y	-2
A	4	Z	1
B	5	X	-1
B	6	Y	-2
C	7	Z	1
C	8	K	-2

My indicator should refer to each building and be calculated with this fraction:

NUMERATOR: number of Ratings under 0 (at most 1 for each Category)

DENOMINATOR: number of Categories in the Building

In the example, for BUILDING A: (look at bold rows)

NUMERATOR=2 (A,1,X,-3 and A,3,Y,-2)

DENOMINATOR=3 (X,Y,Z)

INDICATOR=2/3=66,6%

Suggestions?

Thanks!!

[gandalfgray]

You may try:

count({$<RATING={"<0"}>} distinct CATEGORY)/count(distinct CATEGORY)

as expression

[#3]

Thank you Göran ,

still don't know how and why, but it works!

[#4]

Hi!

Even if the expression works properly, i'm getting quite nervous trying to understand its syntax...

Can you please suggest me how can i get material concerning the building of such expressions.
I'd like to think indipendently on my QView requests...

Thank you!

Best Regards

[gandalfgray]

Hi Giovannone,

There are several ways to learn more about set analysis:

In Qlikview go to help (F1) and search for "set analysis"

There you will have all of the syntax and a few examples.

Another option is to go to http://global.qlik.com/download/ and download documentation in pdf format

About the syntax in my previous post:

The first count() (in the numerator) is modified by a set operator so that only records where RATING is <0 will be counted, and I simply count distinct categories ( where RATING is <0 that is)

(The second count() (in the denominator) is of course just a plain count distinct)