topic Re: Summing top X ranked items in chart in QlikView

Summing top X ranked items in chart

Mon, 23 Sep 2013 16:17:49 GMT

I have searched the community for an answer for this, but other posted solutions do not seem to work for my case. I have the following input. Note: My actual data are spread out across multiple tables using a hash as a key to link customers to projects and bids. They are too big to JOIN in the script, but QV handles the tables nicely without join.

LOAD * INLINE [
Customer, Project, Exclusive, Bid
A, foo, 1, 40
B, foo, 0, 1
C, foo, 0, 2
D, foo, 0, 4
E, foo, 0, 7
F, foo, 0, 10
A, bar, 0, 10
B, bar, 0, 4
A, ded, 1, 30
B, ded, 1, 15
C, ded, 0, 5
D, ded, 0, 5
];

I want my output to have the following columns:

Project = project dimension

SharedSum = This is the sum of the top 4 customer bids with Exclusive = 0 associated with the project on that row. For example, with project "foo", the top 4 would be customer F (bid = 10), E (bid = 7), D (4), C(2). The sum would therefore be 10+7+4+2 = 23. The resulting table will have these values.

Project	ShareSum
bar	14
ded	10
foo	23

However, I cannot get my ShareSum calculation to work properly. Here is what I have, which doesn't work:

=sum( aggr(
if(rank(sum( { < Exclusive={0} > } Bid)) <= 4,
sum( { < Exclusive={0} > } Bid)
), Customer))

Thanks!

Correct answer with help from Oleg Troyansky's comment/blog post:

=sum( aggr(
if(rank(sum( { < Exclusive={0} > } Bid),4) <= 4,
sum( { < Exclusive={0} > } Bid)
), Project, Customer))

Re: Summing top X ranked items in chart

manas_bn — Mon, 23 Sep 2013 20:56:57 GMT

Hello,

If you can change the script you can try the below code. It will create a Rank column for each Project and each Exclusive flag. So all you have to do is use set analysis on the front end. No need of Aggr/If.

Fact:

LOAD *

,RecNo() as ID // Create ID Field

INLINE [

Customer, Project, Exclusive, Bid

A, foo, 1, 40

B, foo, 0, 1

C, foo, 0, 2

D, foo, 0, 4

E, foo, 0, 7

F, foo, 0, 10

A, bar, 0, 10

B, bar, 0, 4

A, ded, 1, 30

B, ded, 1, 15

C, ded, 0, 5

D, ded, 0, 5

];

// Join Rank to Fact table

LEFT JOIN (Fact)

LOAD

,if(Project=Previous(Project),peek(Rank)+1,1) as Rank

RESIDENT Fact

ORDER BY Exclusive,Project, Bid DESC

;

Re: Summing top X ranked items in chart

Mon, 23 Sep 2013 21:18:56 GMT

Manas, that is an elegant solution. But I may have oversimplified my question. Your solution would work if my data were in a single table. My data are in separate tables, one with Customer and Project, and another with Customer and Price, linked by a hash key (it's actually even more complicated than that, but I'm trying to keep it simple for this example). These tables are extremely large, and my attempts at joining them in the script crash QlikView. However, QV handles the data nicely as separate tables in the reports. This forces me to keep them as separate tables joined by a hash key, and perform the Aggr/rank in the report itself.

Re: Summing top X ranked items in chart

Oleg_Troyansky — Mon, 23 Sep 2013 22:47:49 GMT

Check out this blog article:

QlikView Blog Q-Tip #5 – Show Top Performers. Common but not too trivial. | Natural Synergies

enjoy!

Oleg Troyansky

www.masterssummit.com

Re: Re: Summing top X ranked items in chart

Kushal_Chawda — Tue, 24 Sep 2013 05:21:22 GMT

see the attached one

Re: Summing top X ranked items in chart

Tue, 24 Sep 2013 14:57:24 GMT

Oleg Troyansky By golly! After reading your blog post and trying various permutations, I think I got it to work! I applied to to my *real* data set and so far everything looks correct. I still have a little more auditing to do, though.

Here is what I ended up with for my expression (not dimension):

=sum( aggr(
if(rank(sum( { < Exclusive={0} > } Bid),4) <= 4,
sum( { < Exclusive={0} > } Bid)
), Project, Customer))

Basically, I was very close with my original expression; I just needed to have all my dimensions identified in the Aggr function. So just Aggr by Customer was not enough, I had to also include Project to get the correct values.

Reading up on the "4" in the Rank function, I'm still not clear what it is actually doing, but the calculation does not work without it.

4 - Lowest rank on first row, then incremented by one for each row

I might also look into optimizing my data to have precalculated ranked values in a table somewhere, but that might force me to do it on the database side.

Re: Summing top X ranked items in chart

Oleg_Troyansky — Tue, 24 Sep 2013 21:16:08 GMT

Reading up on the "4" in the Rank function, I'm still not clear what it is actually doing, but the calculation does not work without it.

4 - Lowest rank on first row, then incremented by one for each row

Chadd - the parameter 4 makes sure that each element gets a unique rank, even if the value is the same. For example, if the first 3 projects have an identical value = 1,000 you could give them all Rank = 1, or Rank = "1-3", or rank each one of them with a unique number 1, 2, 3. The parameter "4" means the later, - each entry is ranked with a unique number. Otherwise, you are running into a risk of getting more than N top entries (by accident, also 4 in your example)

cheers,

Oleg