topic Re: Expression to find which expression is highest and lowest / dimension. in QlikView

Expression to find which expression is highest and lowest / dimension.

RSvebeck — Fri, 18 Nov 2011 12:50:03 GMT

Hi,

I have a table with one single dimension and 4 advanced expressions.

This is the result in a table:

Item Expr1 Expr2 Expr3 Expr4
A    10     20    10    5
B    15     10    50   50
C     1      6     2   -5

Now - I want a 5th and a 6th expression that gives me which the value of the 4 "main" expression that returns the highest and lowest values.

Expected result:

Item Expr1 Expr2 Expr3 Expr4 Highest Lowest
A       10       20       10       5    20      5
B       15       10       50     50    50     10
C         1        6          2     -5    6      -5

The expressions 1-4 are complicated and based on set analysis, so I would prefere to be able to refer the max and min expressions to the expression aliases like this:

Highest = getmymaxvalue([Expr1],[Expr2],[Expr3],[Expr4])

I need to be able to present this in a table chart (not a pivot) so I can not use the hrank function, I guess...?
...And I would prefere not to use nested if's...

Is it possible?

Best Regards,

Robert Svebeck

Re: Expression to find which expression is highest and lowest / dimension.

RSvebeck — Fri, 18 Nov 2011 14:16:32 GMT

And here is a QVW example of what I mean, where I am using nested if to solve what I need. But in the "real world", I need to compare 10-20 different expressions per dimension, so a solution with nested if is not preferable...there really must be an easier way...

Re: Expression to find which expression is highest and lowest / dimension.

johnw — Fri, 18 Nov 2011 22:25:56 GMT

Robert Svebeck wrote:
Highest = getmymaxvalue([Expr1],[Expr2],[Expr3],[Expr4])

You're SO close. You want the rangemax() and rangemin() functions, and you can refer to either the column labels or numbers, whichever is more convenient for you. So either of these should work (and do in your example):

rangemax(Expr1,Expr2,Expr3,Expr4)
rangemax(column(1),column(2),column(3),column(4))

Re: Expression to find which expression is highest and lowest / dimension.

RSvebeck — Mon, 21 Nov 2011 09:23:54 GMT

Thank you Sir, just what I was looking for. //Robert