# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Not applicable

## Sum largest values each year. Rank, Sum & Aggr?

I'm trying to make a graph which shows the sum of the 3 largest values each year as a percentage of the sum of all values each year. I've tried to use rank, sum and aggr, but it does not work for me. Can someone guide me in the right direction by providing me with a link to a thread where this is discussed (I have not found one that's appropriate, but perhaps you will) or by helping me directly? Pleas find attached example of input data to QV.

1 Solution

Accepted Solutions
Highlighted
MVP

Try this

=Sum({<Key = {"=Aggr(Rank(Sum(Sales)), Year, Company) < 4"}>} Sales)/Sum(Sales)

Where Key is created in the script

Table:

CrossTable(Year, Sales)

[2008],

[2009],

[2010],

[2011],

[2012],

[2013],

[2015],

[2016],

[2017]

FROM

(ooxml, embedded labels, table is QV);

FinalTable:

Company&Year as Key

Resident Table;

DROP Table Table;

7 Replies
Highlighted
MVP

What would be the expected output based on the sample attached?

Highlighted
MVP

Also, do you have years in different columns or do you have an year field?

Highlighted
Master III

maybe you can check if this is correct -

Highlighted
Not applicable

Almost. I have had this problem before. It seems as if the formula sums more values than the three biggest values... The first year is correct, but the second is not correct when I compare to my excel sheet.

Highlighted
MVP

Company A repeats twice, is this by mistake?

Highlighted
MVP

Try this

=Sum({<Key = {"=Aggr(Rank(Sum(Sales)), Year, Company) < 4"}>} Sales)/Sum(Sales)

Where Key is created in the script

Table:

CrossTable(Year, Sales)

[2008],

[2009],

[2010],

[2011],

[2012],

[2013],

[2015],

[2016],

[2017]

FROM

(ooxml, embedded labels, table is QV);

FinalTable: