The nodistinct qualifier and the Aggr function

[ArturoMuñoz]

There's a lot of content written about the Aggr function in the Qlik Community and in this blog as well.  No doubt why, Aggr is one of the most powerful, and sometimes intriguing, functions we have in Qlik.

Today, I would like to explain the use of nodistinct and Aggr and the importance of granularity with a simple example.

I have a table containing sales data, I load that table like this:

ProductData:

LOAD * inline [

Customer|Product|UnitSales|UnitPrice
Astrida|AA|4|16
Astrida|AA|10|15
Astrida|BB|9|9
Betacab|BB|5|10
Betacab|CC|2|20
Betacab|DD|25|25
Canutility|AA|8|15
Canutility|CC||19

] (delimiter is '|');

My goal is to display a table in Qlik (Sense or QlikView) that shows the sales by customer and product.  I need to add a column that contains the percentage that each product represents over the total sales to each customer, I called it Share, the resulting table should look like this:

To create a table like that, I could use the Aggr function, and I could write an expression that calculates the sales amount for each pair “customer product” and then divide that number by the total sales amount by customer. 

sum(UnitSales*UnitPrice)/only(aggr(sum(UnitSales*UnitPrice), Customer))

If I use that expression, then the resulting table looks like the picture below, where only the first pair "customer product" contains a valid result. 

Understanding granularity

The issue here is granularity. My chart has more dimensions - is more granular, contains Customer and Product -  than my Aggr function, only contains Customer.

The expression only(aggr(sum(UnitSales*UnitPrice), Customer) returns an array of three values, one for each customer. 

As you can see in the table above, when an aggr array containing three values goes into the chart with higher granularity, only the fist row for the parent dimension contains a valid expression. The rest of the rows are performing a calculation that returns null. Is performing something like "sum(UnitSales*UnitPrice) divided by null"

Using nodistinct

Qlik's Aggr help page: If the expression argument is preceded by the nodistinct qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure. If there is only one dimension, the aggr function will return an array with the same number of elements as there are rows in the source data. 

In other words, if the nodistinct qualifier is used, then our example's aggr array will contain 7 rows of data, it will repeat the value for each customer across the rows that contain that customer:

Finally, to complete our example and to get a table that looks exactly as the one requested, we just need to add the nodistinct qualifier to our Aggr function:

sum(UnitSales*UnitPrice)/only(aggr(nodistinct sum(UnitSales*UnitPrice), Customer))

I hope this makes sense and you find it useful.

Arturo (@arturoqv)