15 June 2022 Aggregating data in Qlik Sense with the Aggr() function Share this message Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about aggregations in Qlik Sense with the Aggr() function: The correct answer is C: Avg(Aggr(Sum(NumberOfProducts * ProductPrice), OrderID)) To explain the answer let’s see what it is that is exactly wanted. We have a straight table with an order overview, in which we can see the PartnerID, how many orders they have placed, the lowest order value and the highest order value. Now the request is to add the average order value to this as well. Going trough the possible answers we can see that while answer A will work for the first four partners, it will quickly run into troubles calculating the average if there are more then two orders. And this is where the Aggr() function comes into play. To properly use the Aggr() function we need to have a look at the data model from which we can determine that the Fact table will look like this: So even without knowing the true contents of the table in this question, we can get an idea of the contents and what to do next. To be able to calculate the average order value per PartnerID, we need to calculate the total value of each OrderID first. This is done by multiplying the NumberOfProducts and the ProductPrice. Then if we total those values per OrderID we know what the total value of each OrderID is. To finalize we can then get the average of all these OrderID totals. And this is exactly what Avg(Aggr(Sum(NumberOfProducts * ProductPrice), OrderID)) does. The Aggr() function syntax is: Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter}) So you will aggregate an expression, based on a StructuredParameter. The StructuredParameter is the dimension on which you would like to aggregate the expression. In our current example this is OrderID. To brake it down: We first calculate the value of the products: Then calculate the aggregated value of the products per OrderID: This basically creates an in memory table containing each OrderID and the total value of the OrderID. And now we can finally use the average function to calculate the average over the OrderID’s: Some other things to keep in mind: It is possible to aggregate on more then one dimension. And it is also possible to sort these. So if you use MonthYear as dimension for example, it is possible to sort these ascending or descending however it is needed. As seen in the Syntax, Aggr() can also use set expressions. So for example: {<Year = {2022}>} can be added to the Aggr() function. The standard calculation of the Aggr() syntax is a distinct aggregation. So for each distinct value of the dimension you would like to aggregate on, it will give the result. However if you have a repeating value in the dimension you can add NODISTINCT to the function. That’s it for this week. See you next Friday? More from the Bitmetric team Take your Qlik skills to the next level! Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level. Join the team! Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening. Friday Qlik Test Prep Functions Performance Solution How can we help? Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have. Call us Mail us 29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution 22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization 11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik
29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution
22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization
11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik