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:

### 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 as following:

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**:

Now we can add this to the table to get the results of the average order value per **PartnerID**, since **PartnerID **is already a dimension within the table.

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? And remember:

- If you have suggestions for questions, we love to hear from you via WhatsApp or at info@bitmetric.nl
- If you’re enjoying these questions and want to work on stuff like this every day (but a bit more challenging), we’re always on the lookout for new colleagues. Check our job openings here.