Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
You may try:
count({$<RATING={"<0"}>} distinct CATEGORY)/count(distinct CATEGORY)
as expression
Thank you Göran ,
still don't know how and why, but it works!
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
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)