Sum Weekly Rank

jzimolong — Sun, 04 Dec 2016 12:41:06 GMT

I have the following Straight Table chart, which displays a teams Points Scored and Rank on a weekly basis:

Team	Week	Points	Rank	Win	Loss
BBD	1	97.4	10	0	9
Ultra	1	102.92	9	1	8
Amherst	1	107.52	8	2	7
Kodiak	1	108.8	7	3	6
Biscuits	1	109.36	6	4	5
Divorced	1	110.76	5	5	4
Terror	1	113.8	4	6	3
Spread	1	134.5	3	7	2
NIO	1	137.52	2	8	1
Nirvana	1	144.76	1	9	0
Kodiak	2	72.56	10	0	9
BBD	2	78.76	9	1	8
Biscuits	2	81.52	8	2	7
Spread	2	83.82	7	3	6
Terror	2	89.08	6	4	5
Ultra	2	90.56	5	5	4
Divorced	2	121.56	4	6	3
Nirvana	2	126.22	3	7	2
NIO	2	129.82	2	8	1
Amherst	2	140	1	9	0
Amherst	3	75.84	10	0	9
Kodiak	3	90.86	9	1	8
BBD	3	93.38	8	2	7
Biscuits	3	100.58	7	3	6
Terror	3	106.24	6	4	5
Spread	3	109.6	5	5	4
Nirvana	3	111.7	4	6	3
Ultra	3	123.8	3	7	2
Divorced	3	138.46	2	8	1
NIO	3	157.34	1	9	0

Dimensions: Team, Week

Expressions:

Points: =Sum(Points)
Rank: =RANK(Sum(Points))
Win: =10-RANK(Sum(Points))
Loss: =RANK(Sum(Points))-1

Sort: Chart is sorted by Week (Ascending, then Points (Ascending) - To make sure the Rank is set properly.

I use the Rank to equate a team's Win/Loss versus the other teams each week. This simply shows their record against the rest of the league teams each week, based on Rank of how many points scored.

What I'm trying to get to is an aggregated Win/Loss, so the table would look like this, which essentially sums the weekly Win/Loss above into an aggregated chart, to show team's performance for the entire season, based on weekly scores. I suspect the AGGR function would be the right path, but I'm struggling to figure it out.

Team	Win	Loss
NIO	25	2
Nirvana	22	5
Divorced	19	8
Spread	15	12
Terror	14	13
Ultra	13	14
Amherst	11	16
Biscuits	9	18
Kodiak	4	23
BBD	3	24

Here is Load Script with sample data:

WeeklyPoints:

LOAD * Inline [

Team, Week, Points

BBD, 1, 97.4

Ultra, 1, 102.92

Amherst, 1, 107.52

Kodiak, 1, 108.8

Biscuits, 1, 109.36

Divorced, 1, 110.76

Terror, 1, 113.8

Spread, 1, 134.5

NIO, 1, 137.52

Nirvana, 1, 144.76

Kodiak, 2, 72.56

BBD, 2, 78.76

Biscuits, 2, 81.52

Spread, 2, 83.82

Terror, 2, 89.08

Ultra, 2, 90.56

Divorced, 2, 121.56

Nirvana, 2, 126.22

NIO, 2, 129.82

Amherst, 2, 140

Amherst, 3, 75.84

Kodiak, 3, 90.86

BBD, 3, 93.38

Biscuits, 3, 100.58

Terror, 3, 106.24

Spread, 3, 109.6

Nirvana, 3, 111.7

Ultra, 3, 123.8

Divorced, 3, 138.46

NIO, 3, 157.34

];

Re: Sum Weekly Rank

Frank_Hartmann — Sun, 04 Dec 2016 13:49:50 GMT

Dimension= Team

Expression1= sum(aggr(10-RANK(sum(aggr(sum(Points),Week,Team))),Week,Team))

Expression2= sum(aggr(RANK(sum(aggr(sum(Points),Week,Team)))-1,Week,Team))

hope this helps

Re: Sum Weekly Rank

jzimolong — Sun, 04 Dec 2016 14:39:23 GMT

Thank you Frank, this is working!

topic Sum Weekly Rank in QlikView

Sum Weekly Rank

Re: Sum Weekly Rank

Re: Sum Weekly Rank