This article is written by Stephen Redmond, the author of Mastering QlikView. There is a great skill in creating the right expression to calculate the right answer. Being able to do this in all circumstances relies on having a good knowledge of creating advanced expressions. Of course, the best path to mastery in this subject is actually getting out and doing it, but there is a great argument here for regularly practicing with dummy or test datasets.

(For more resources related to this topic, see here.)

When presented with a problem that needs to be solved, all the QlikView masters will not necessarily know immediately how to answer it. What they will have though is a very good idea of where to start, that is, what to try and what not to try. This is what I hope to impart to you here. Knowing how to create many advanced expressions will arm you to know where to apply them—and where not to apply them.

This is one area of QlikView that is alien to many people. For some reason, they fear the whole idea of concepts such as Aggr. However, the reality is that these concepts are actually very simple and supremely logical. Once you get your head around them, you will wonder what all the fuss was about.

No to nodistinct

The Aggr function has as an optional clause, that is, the possibility of stating that the aggregation will be either distinct or nodistinct.

The default option is distinct, and as such, is rarely ever stated. In this default operation, the aggregation will only produce distinct results for every combination of dimensions—just as you would expect from a normal chart or straight table.

The nodistinct option only makes sense within a chart, one that has more dimensions than are in the Aggr statement. In this case, the granularity of the chart is lower than the granularity of Aggr, and therefore, QlikView will only calculate that Aggr for the first occurrence of lower granularity dimensions and will return null for the other rows. If we specify nodistinct, the same result will be calculated across all of the lower granularity dimensions.

This can be difficult to understand without seeing an example, so let’s look at a common use case for this option. We will start with a dataset:

ProductSales:
Load * Inline [
Product, Territory, Year, Sales
Product A, Territory A, 2013, 100
Product B, Territory A, 2013, 110
Product A, Territory B, 2013, 120
Product B, Territory B, 2013, 130
Product A, Territory A, 2014, 140
Product B, Territory A, 2014, 150
Product A, Territory B, 2014, 160
Product B, Territory B, 2014, 170
];

We will build a report from this data using a pivot table:

Now, we want to bring the value in the Total column into a new column under each year, perhaps to calculate a percentage for each year. We might think that, because the total is the sum for each Product and Territory, we might use an Aggr in the following manner:

Sum(Aggr(Sum(Sales), Product, Territory))

However, as stated previously, because the chart includes an additional dimension (Year) than Aggr, the expression will only be calculated for the first occurrence of each of the lower granularity dimensions (in this case, for Year = 2013):

The commonly suggested fix for this is to use Aggr without Sum and with nodistinct as shown:

Aggr(NoDistinct Sum(Sales), Product, Territory)

This will allow the Aggr expression to be calculated across all the Year dimension values, and at first, it will appear to solve the problem:

The problem occurs when we decide to have a total row on this chart:

As there is no aggregation function surrounding Aggr, it does not total correctly at the Product or Territory dimensions. We can’t add an aggregation function, such as Sum, because it will break one of the other totals.

However, there is something different that we can do; something that doesn’t involve Aggr at all! We can use our old friend Total:

Sum(Total<Product, Territory> Sales)

This will calculate correctly at all the levels:

There might be other use cases for using a nodistinct clause in Aggr, but they should be reviewed to see whether a simpler Total will work instead.

Summary

We discussed an important function, the Aggr function. We now know that the Aggr function is extremely useful, but we don’t need to apply it in all circumstances where we have vertical calculations.

Resources for Article:

Further resources on this subject:

• Common QlikView script errors [article]
• Introducing QlikView elements [article]
• Creating sheet objects and starting new list using Qlikview 11 [article]