Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello frnds,
can any one explain me what is FirstSortValue() and where we can use it.
with an example
please
Hi
watch this video it explains about all your doubts
QlikView | Expressions | AGGR Function with Max and FirstSortedValue function - YouTube
Regards
Harsha
it will give the first value of expression based on the sort wait ,sort wait should be numeric
date and sales u have fields
date:
1/1/2014
2/1/2014
3/1/2014
4/1/2014
5/1/2014
sales:
100
200
300
600
500
here u want see the top value and bottom value
firstsortedvalue(date,-sales) in chart if u written then it gives top value
firstsortedvalue(date,sales) in chart if u written then it gives bottom value
check this
Hello Rahim,
here you go
Firstsortedvalue()
This function returns the first value of the expression sorted by corresponding sort-weight when the expression is executed over a number of records as defined by a group by clause. Sort-weight returns a numeric value where the lowest value will render the corresponding value of the expression to be sorted first.
Syntax
firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])
Let us take a scenario, where we have to identify Product Name that sold the most during selected period by company. To do so, we have to use AGGR function in conjunction with function FIRSTSORTEDVALUE().
FIRSTSORTEDVALUE function return the first value of expression sorted by corresponding sort-weight. Sort weight should be numeric value and it returns NULL value if more than one expression value share the same sort order so if there is tie in sorting it returns NULL value unless you do some thing else to overcome this issue.
To extract the last sorted value which is opposite to first sorted value, we need to add a minus sign (-) to sort weight expression. We can also use set analysis and Total in FIRSTSORTEDVALUE function.
Syntax:- FIRSTSORTVALUE( Field, Sort-Weight)
As mentioned above, to identify name of Product based on highest sales product by company, we have to write expression like :
=FIRSTSORTEDVALUE (Product, – aggr(sum(Sale),Company_Name,Product) )
In above expression, AGGR statement generates a virtual table that has total sales by company and product. Then, FIRSTSORTEDVALUE function identifies the Product Name from the virtual table for highest sale. Because aggr function is preceded by minus sign (-), it will return product name against highest sales.
Hello Rahim,
You can use FirstSortedValue both in the script and user interface (UI) of QlikView.
FirstSortedValue works when loading aggregated (grouped) data
Here we have a simple table showing when a customer bought a particular product:
Loading the data grouped by Customer we can use the DayOfSale as the sort-weight, position 1 and return the value of product.
Customer:
Load
Customer
,FirstSortedValue(Product,DayOfSale,1) as FirstProductBought
Resident Customers_Temp
Group by Customer