The main purpose behind creating any dashboard is to summarize and add information in a way that can be communicated visually and clearly.. Traditionally, this summarization and aggregation used to happen outside the scope of the dashboard tool.
This is what a traditional BI architecture looks like:
Traditional dashbaording process
The input sources are usually transactional in nature and all the summaries that are normally done in Datamarts. But nevertheless, Qlikview works on a different architecture, connects directly to input systems and can add layers internally so you don't need datamarts or data warehouse. Here is a representation of what it might look like:
Since Qlikview offers an option to perform aggregation within, you need a robust aggregation function. The idea of this post is to introduce you to the use of the AGGR function () in Qlikview.
The (s) function (it is) added (s) it is developed in such a way that we can use it for multiple applications as a conditional return of value, multiple value return or many more. This advanced add-on feature can improve our dashboards and reports multiple times.
What is AGGR?
AGGR is a very powerful aggregation function in QLikview that is sometimes overlooked due to lack of understanding or familiarity. AGGR statements create a temporary table of results, with an expression that can be grouped in one or more dimensions. Is similar to “Group by” and SQL.
The results of the virtual table can be used by external aggregate functions. Can be used in both expression and dimension (calculated dimension).
Syntax:
aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})
When should we use AGGR?
- Whenever we do two-step aggregation as Sum of sales of the best-selling product per company the Highest average sale by Product and Company, in both states we are doing two aggregations.
- We want to identify the top population / lower in segment.
- Gets more powerful when used with ensemble analysis, Rank (), FirstSortedValue ().
Data set example:
Note that I have chosen transactional data to highlight the aggregation aspects in a better way.
Read also: Create a simple and effective sales dashboard with Qlikview
Examples of AGGR:
Now, We will explore how to use the simple AGGR function in CHART, Pivot Table o Straight tables. Below are examples where we can use the AGGR function:
- Sales of the best-selling product
- Highest average product sale
Total company sales can be calculated using the SUM function () simply (as SUM (Sale)). But nevertheless, we need to calculate the sale of the best-selling product by a company. To solve this we will use the AGGR function doing two aggregations in one step.
To achieve the sale of the best-selling product for each company, we need to have those dimensions (Company_Name y Product) in our AGGR statement ....
= Max (aggr (sum (Sale), Company name, Product))
As mentioned earlier, when we use the AGGR function, create a temporary table. In this case, QlikView has calculated the sale of each Product by Company. From this virtual table we have to identify the maximum sale value and the MAX function does it ().
The next scenario is to calculate the highest average product sale per company. To solve this, we need to have an expression for the average sale like “SUM (Sale) / Count (Product)” and the dimensions required within our AGGR statement:
= Max (aggr (sum (sale) / count (product), company name, product))
Also in this case, Qlikview will generate a temporary table for the average sale by Product and Company and publish that the MAX function will identify the largest value.
Read also: 2 Simple tricks to improve the density of information in your Qlikview dashboards
AGGR with ensemble analysis:
In the examples above, the expression was working with current select sets where, as if we wanted to compare the previous year's sale of the best-selling product with the current year, then we have to use the set analysis methodology in our expression.
In case you're new to set analysis, check my previous post “Set analysis in QlikView – simplified!”.
To return the sales of the best-selling product from the previous year, we need to write a set expression:
= Max ({} aggr (sum ({} Sale), Company_Name, Product) )
To understand it more clearly, it will look like this:
= Max ({} aggr (sum ({} Sale), Company_Name, Product))
In the above expression, I have used the set expression twice, one for the MAX function and one for AGGR.
AGGR con FIRSTSORTEDVALUE :
Let's take a stage, where we have to identify the name of the product that sold the most during the period selected by company. To do it, We have to use AGGR function together with function FIRSTSORTEDVALUE ().
FIRST VALUE The function returns the first expression value ordered by the corresponding rank weight. The sort weight must be a numeric value and returns a NULL value if more than one expression value share the same sort order, so if there is a tie in the classification, returns NULL value unless you do something else to overcome this problem.
To extract the last ordered value that is opposite to the first ordered value, we need to add a minus sign (-) to order the weight expression. We can also use the analysis of sets and Total in FIRST VALUE function.
Syntax: – FIRSTSORTVALUE (Field, Sort-Weight)
As mentioned earlier, to identify the product name based on the best-selling product by company, we have to write an expression like:
= FIRSTSORTEDVALUE (Product, – aggr (sum (Sale), Company name, Product))
In the above expression, the AGGR statement generates a virtual table that has the total sales by company and product. Later, the FIRSTSORTEDVALUE function identifies the Product name of the virtual table for greater sale. Because the aggr function is preceded by the minus sign (-), will return the product name against the highest sales.
Identify upper segment / less than specific
One of the best use case scenarios where I use the AGGR function is to identify the top segment / inferior. Here, we will identify the 2 main products over the years 2012 Y 2013 inside a text object as seen below:
To achieve this, we will use the Concat functions () and Rank () along with the AGGR statement. The complete expression is as follows:
= concat (IF (aggr (Rank (sum (Sale)), Product) <= 2, Product & chr (10)))
Expression “aggr (Rank (Sum (Sale)), Product) ”, create a virtual table with Product and Rank in front of your sale. The next step is to add our IF statement to request that we only receive the 2 main products in the result. Therefore, if the range in our virtual AGGR table is <= 2, show product; on the contrary, return null ().
Finally, we want to display the results of this in a text object. We have used the CONCAT function () to concatenate the 2 Product values together. To add a separate line between both products, we used chr (10).
This post was an example where we introduced methods for using AGGR in Qlikview. Do you have any more tricks to make AGGR more interesting?? If that is not the case, Do you think this article will allow you to use the AGGR function in your next control panel?
Let me know what you think about using this function in QlikView.
