topic FirstSortValue in QlikView

FirstSortValue

Thu, 08 Jan 2015 07:20:20 GMT

hello frnds,

can any one explain me what is FirstSortValue() and where we can use it.

with an example

please

Re: FirstSortValue

Anonymous — Thu, 08 Jan 2015 07:26:10 GMT

watch this video it explains about all your doubts

QlikView | Expressions | AGGR Function with Max and FirstSortedValue function - YouTube

Regards

Harsha

Re: FirstSortValue

Thu, 08 Jan 2015 07:32:39 GMT

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

Re: FirstSortValue

arulsettu — Thu, 08 Jan 2015 07:33:35 GMT

check this

FirstSortedValue()

Re: FirstSortValue

Thu, 08 Jan 2015 07:44:44 GMT

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.

Re: FirstSortValue

tyagishaila — Thu, 08 Jan 2015 09:01:47 GMT

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