OK, we need to work from the inside to the outside on this one.
Let's start with this part:
aggr(rank(sum(profit))<4,year,branchName)
And let's first consider a simpler case, this:
aggr(sum(profit),year,branchName)
An aggregation is like an internal chart. In this case, it's like we have an internal chart with two dimensions, year and branchName. For each year and branch name, we want to calculate the total profit. Think of it as a pivot table with branchName on the left and year on the top. Go ahead and built it if you want to see it. That's what the aggr() is doing.
Then we add in the rank() function:
aggr(rank(sum(profit)),year,branchName)
This pivot table IS in the posted example, the top left chart. So this is what the aggr() is doing internally, without actually building a chart, ranking branchNames by the sum(profit) for each year.
Now, we only want to track the top three ranks. That can be expressed like this:
And I just realized that the expression in the final chart can be written like that rather than how I wrote it in the example, which should simplify things a little. However, for the purpose of counting, we don't care WHAT the rank actually is, only that it is in the range of 1-3. So we can simplify to this:
aggr(if(rank(sum(profit))<4,1,0),year,branchName)
So if rank(sum(profit))<4, it returns 1, otherwise it returns 0. However, in QlikView, true is -1 and false is 0. So the condition itself, without an if(), will return -1 or 0. That lets us simplify to this:
aggr(-(rank(sum(profit))<4),year,branchName)
But it's inefficient to take the negative for every "row and column" in our table. So we'll remove it for now, and just remember that our number is currently negative instead of positive, and we'll have to fix that. That brings us to the sub-expression we started with:
aggr(rank(sum(profit))<4,year,branchName)
Now let's look at our second aggregation:
aggr(-sum(...),branchName)
The expression we already made goes where the "..." are. So this second aggr() is using a data set which is an internal table of each year and branchName, with a value of -1 or 0 depending on whether it's top three ranked that year or not. So THIS aggr() then takes that data, groups by branchName, sums up the -1s, and then fixes the sign. What it produces, then, is an internal table of branchName vs. the count of the number of years that branch has been in the top 3 for profit. Since our REAL chart has branchName as the first real dimension, that maps to only one row of this internal table, and therefore it returns ONLY the matching count of years. Oh, and I probably should have put the minus sign all the way on the outside:
