topic Count Rank in QlikView

Count Rank

Mon, 05 Jul 2010 09:01:00 GMT

Hi, I need your help..

I want to show, how many times a branch is in top 3.

Please helpp..

Count Rank

Mon, 05 Jul 2010 09:56:20 GMT

I've attached an update chart - should be just a case of totalling the last column...

Count Rank

Mon, 05 Jul 2010 10:25:36 GMT

Thank you for your helpfulness..

But I should use with aggregate function. Otherwise I can not show results for both years and branches.

I m looking for your suggestions..

Count Rank

johnw — Tue, 06 Jul 2010 19:28:33 GMT

To produce the chart you requested:

Dimension 1 = branchName
Dimension 2 = aggr(-sum(aggr(rank(sum(profit))<4,year,branchName)),branchName)
Dimension 3 = year
Expression = if(aggr(rank(sum(profit)),year,branchName)<4
,aggr(rank(sum(profit)),year,branchName))

See attached.

Count Rank

Wed, 07 Jul 2010 15:22:06 GMT

Dear John,

Thanks for your reply, This was exactly what I want to do.

I had lost hope of solving this problem, I am grateful to you, thanks.

Can you explain the 2 nd dimension if you have time?

Count Rank

johnw — Wed, 07 Jul 2010 17:31:19 GMT

aggr(-sum(aggr(rank(sum(profit))<4,year,branchName)),branchName)

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:

aggr(if(rank(sum(profit))<4,rank(sum(profit))),year,branchName)

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:

-aggr(sum(aggr(rank(sum(profit))<4,year,branchName)),branchName)

So then the slightly-simplified chart would be this:

Dimension 1 = branchName
Dimension 2 = -aggr(sum(aggr(rank(sum(profit))<4,year,branchName)),branchName)
Dimension 3 = year
Expression = aggr(if(rank(sum(profit))<4,rank(sum(profit))),year,branchName)

Count Rank

Thu, 08 Jul 2010 09:20:02 GMT

It is so clear, thank you very much.

I will keep this text as a lecture note on aggr and rank functions.

Thanks again.

Count Rank

Fri, 09 Jul 2010 22:05:22 GMT

Hi, John !

If I want to show, how many times a branch is in first position sequentially ?

I`m a newbie on Qlikview and I need and want to learn it.

Best Regards.

Count Rank

johnw — Sat, 10 Jul 2010 00:18:18 GMT


Mombuca01 wrote:If I want to show, how many times a branch is in first position sequentially ?

I don't understand the question. If we use the sample data we were given, what sort of output chart are you asking for?

Count Rank

Sat, 10 Jul 2010 03:35:07 GMT

Example: Ray was #1 for 2 times in sequentially....

Position Name

1 Paul

1 Ray

1 Paul

1 Ray

Re: Count Rank

Tue, 16 Oct 2012 10:07:00 GMT

Hai Please Find The Attach ment