Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
];
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
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
Thank you Frank, this is working!