Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
ArturoMuñoz
Employee
Employee

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:

goal.png

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. 

issue.png

 

 

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:

 

aggr.png

 

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)

 

4 Comments