Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to get something like the "small function" in Excel?
F.e. if you have these numbers:
in Excel, small(num,4) = 3
in Qlikview(11), min(num,4) = 5
Hi,
min function is working like small.
what's your problem.
Hi.
There is much more powerful function firstsortedvalue():
firstsortedvalue([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])
returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.
Just use the same field for both expression and sort_weight parameters.
Hi.
In Excel there is an order of cells in range.
You could add order as separate field and use it for sorting:
LOAD * INLINE [
A, B, num, order
a1, bb, 1, 1
a2, bb, 2, 2
a3, bb, 3, 3
a4, bb, 3, 4
a5, bb, 3, 5
a6, bb, 5, 6
a7, bb, 5, 7
a8, bb, 6, 8
a9, bb, 7, 9
];
Then just use =firstsortedvalue(num, order, 4)
A bit ugly but haven't got anything more.
Maybe it would be better to ask the real question, not just how to replace the Excel function.
Thanks for the help, I already got it to work.
If I just used firstsortedvalue(num,num,n) then in this case it gives a null value for the 3's and 5's.
The way I got it to work is by using firstsortedvalue(num,num+date/100000000,n)
In this case my dates were formated like yyyymmdd. So this way each record has a unique value, and therefore you don't run into problems when you have values that occur more than once.