Ranking, exclude results of expression

[curiousfellow]

I want to rank the difference of values from different years.

However, I want to exlude the difference from ranking when the value of previous year = 0

The results in a chart show no ranking values for the excluded values, but the ranking seems to start with these excluded values.

So when there are two products with the value 0 in previous year, the ranking number starts with 3.

How to achieve that ranking starts with 1 ?

[Anil_Babu_Samineni]

Like this?

[swuehl]

Maybe using an expression like

=Aggr({<key = {"=sum({<prodyear={2016}>}value )> 0"}>}

Rank(sum({<prodyear={2017}>}value )-sum({<prodyear={2016}>}value ),4,2)

,key)

key	2016	2017	difference	Rank
A	15	10	-5	1
B	25	18	-7	2
C	35	19	-16	3
D	0	20	20
E	0	21	21
F	45	23	-22	4
G	65	24	-41	5
H	75	25	-50	6
I	85	26	-59	7
J	95	27	-68	8

[curiousfellow]

Strange, when I load your QWV in Qlik (version 11.20) key D and E are still ranked.

and in the expression editor the expression is full of red lines

[swuehl]

AFAIR, the syntax using set analysis in the Aggr() context was introduced in later versions.

Let me check what can be done in QV11.20

[swuehl · Accepted solution]

Try

Aggr(

Rank(

sum({<key = {"=sum({<prodyear={2016}>}value )>0"}, prodyear={2017}>}value )

-sum({<key = {"=sum({<prodyear={2016}>}value )>0"}, prodyear={2016}>}value ),4,2)

,key)

[curiousfellow]

Thank you Stefan, this one works   Sometimes the set analysis is still rocket science for me.

Within a few weeks we will upgrade to version 12.

I allready tried to find information about changes between version 11.xx and 12.

Didn't see anything about changes in syntax of Set Analysis.

Where to find, important information like this ?

[swuehl]

Johan,

this information should be part of the change notes attached to each release.

But if I say should, it doesn't mean it actually is.

AFAIR, the syntax for aggr() described the possibility to use a set expression for a long time (also in 11.20), but it never really worked (as you see).

Since version 12, it is working.

Not sure if there is a change note describing the fix, though.