Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
jzimolong
Contributor II

Sum Weekly Rank

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

     

TeamWeekPointsRankWinLoss
BBD197.41009
Ultra1102.92918
Amherst1107.52827
Kodiak1108.8736
Biscuits1109.36645
Divorced1110.76554
Terror1113.8463
Spread1134.5372
NIO1137.52281
Nirvana1144.76190
Kodiak272.561009
BBD278.76918
Biscuits281.52827
Spread283.82736
Terror289.08645
Ultra290.56554
Divorced2121.56463
Nirvana2126.22372
NIO2129.82281
Amherst2140190
Amherst375.841009
Kodiak390.86918
BBD393.38827
Biscuits3100.58736
Terror3106.24645
Spread3109.6554
Nirvana3111.7463
Ultra3123.8372
Divorced3138.46281
NIO3157.34190

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.

   

TeamWinLoss
NIO252
Nirvana225
Divorced198
Spread1512
Terror1413
Ultra1314
Amherst1116
Biscuits918
Kodiak423
BBD324

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

];

1 Solution

Accepted Solutions
Highlighted
Frank_Hartmann
Honored Contributor II

Re: Sum Weekly Rank

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

View solution in original post

2 Replies
Highlighted
Frank_Hartmann
Honored Contributor II

Re: Sum Weekly Rank

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

View solution in original post

jzimolong
Contributor II

Re: Sum Weekly Rank

Thank you Frank, this is working!