Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

The way to achieve your own success is the willingness to help somebody else. Go for it!

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- what is the use of aggr function in qlikview ? why...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2014-07-29
01:15 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

what is the use of aggr function in qlikview ? why we use ?

what is the use of aggr function in qlikview ? why we use ?

- Tags:
- new_to_qlikview

5,629 Views

4 Replies

tresesco

MVP

2014-07-29
01:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

417 Views

avinashelite

MVP

2014-07-29
01:24 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

417 Views

Not applicable

2014-07-29
01:24 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

These functions can only be used in field lists for *Load* statements with a group by clause. We use aggregation function in chart to group the values of the chart.The basic Aggregation function is

sum([distinct]expression)

Returns the sum of expression over a number of records as defined by a group by clause. If the word distinct occurs before the expression, all duplicates will be disregarded.

Example:

Load Month, sum(Sales) as SalesPerMonth

from abc.csv group by month;

min( expression[, rank] )

Returns the minimum numeric value of expression encountered over a number of records as defined by a group by clause. Rank defaults to 1 which corresponds to the lowest value. By specifying rank as 2 the second lowest value will be returned. If rank is 3 the third lowest value will be returned and so on.

Examples:

Load Month, min(Sales) as SmallestSalePerMonth from abc.csv group by Month;

Load Month, min(Sales, 2) as SecondSmallestSalePerMonth from abc.csv group by Month;

max( expression[, rank] )

Returns the maximum numeric value of expression encountered over a number of records as defined by a group by clause. Rank defaults to 1 which corresponds to the highest value. By specifying rank as 2 the second highest value will be returned. If rank is 3 the third highest value will be returned and so on.

Examples:

Load Month, max(Sales) as LargestSalePerMonth from abc.csv group by Month;

Load Month, max(Sales, 2) as SecondLargestSalePerMonth from abc.csv group by Month;

only(expression )

If expression over a number of records, as defined by a group by clause, contains only one numeric value, that value is returned. Else, NULL is returned.

Example:

Load Month, only(Price) as OnlyPriceSoldFor from abc.csv group by Month;

mode(expression )

Returns the mode value, i.e. the most commonly occurring value, of expression over a number of records, as defined by a group by clause. If more than one value is equally commonly occurring, NULL is returned. Mode can return numeric values as well as text values.

Examples:

Load Month, mode( ErrorNumber ) as MostCommonErrorNumber from abc.csv group by Month;

Load Month, mode( Product ) as ProductMostOftenSold from abc.csv group by Month;

firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])

Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. 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, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.

Example:

Load Customer,

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv

group by Customer;

just try to use all the functions.

417 Views

evan_kurowski

Specialist

2014-08-07
10:59 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello manojqlik,

Let's clarify if you are referring to the Aggr() function, which is one of several aggregation functions available to the QlikView user. One of the best things that helped me with Aggr() was to understand that its results are an array.

Aggr() in its most basic terms can be thought of as the user-interface equivalent to a GroupBy clause that is applied in QV/SQL scripting.

**The user-interface expression:**

Aggr( Sum(AMOUNT), Date, Person)

**could essentially be equivalent to the scripted result:**

LOAD Sum(AMOUNT) GROUP BY Date, Person

The difference between the two is that the scripted GroupBy result needs to be written to table during script execution, while the Aggr() results populate dynamically when interacting with the user-interface.

417 Views