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 Added FunctionThe aggregate function is a key concept in economics that represents the relationship between the total production of goods and services in an economy and the price level. This function helps to understand how aggregate supply and demand vary in response to changes in factors such as fiscal and monetary policy. Its analysis is essential for the formulation of economic strategies and the prediction of economic cycles.... Advanced can improve our dashboards and reports on several occasions.
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. It can be used both in expression and in dimension"Dimension" It is a term that is used in various disciplines, such as physics, Mathematics and philosophy. It refers to the extent to which an object or phenomenon can be analyzed or described. In physics, for instance, there is talk of spatial and temporal dimensions, while in mathematics it can refer to the number of coordinates necessary to represent a space. Understanding it is fundamental to the study and... (calculated dimension).
Syntax:
aggr ([ distinctThe word "DISTINCT" in English it translates into Spanish as "different" O "different". In the field of programming and databases, especially in SQL, Used to remove duplicates in query results. When applying the DISTINCT clause, only the unique values of a dataset are obtained, which facilitates the analysis and presentation of relevant and non-redundant information.... | 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 nullThe term "NULL" It is used in programming and databases to represent a null or non-existent value. Its main function is to indicate that a variable does not have a value assigned to it or that a piece of data is not available. And SQL, for instance, Used to manage records that lack information in certain columns. Understanding the use of "NULL" It is essential to avoid errors in data manipulation and... ().
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 feature in your next panelA panel is a group of experts that meets to discuss and analyze a specific topic. These forums are common at conferences, seminars and public debates, where participants share their knowledge and perspectives. Panels can address a variety of areas, from science to politics, and its objective is to encourage the exchange of ideas and critical reflection among the attendees.... Control?
Let me know what you think about using this function in QlikView.
