Count Occurrences in Top N with Second Condition

[jzimolong]

I'm trying to create a chart to summarize how many times a Team scores in the top 5 for points each week (10 teams), but losses their respective game.  I've included a Load Script with sample data.  This sample data contains six weeks of results, and the end chart should look like first image below.  I thought this would be rather simple, but have been struggling with it.  Any suggestions are appreciated.

End Results:  for each time, how many times they scored in the top 5 for points, but lost their match

Data Table: I'm struggling with the image, but it's a table box sorted by Year (Asc), Week (Asc) & Pts (Desc)

• Team
• Year
• Week
• Pts Scored
• Game Results "W" win  "L" lost

 Sample Data:

GameResults:
Load * inline
[
Team,Year,Week,Pts,Result
Beer,2007,1,112,W
NIO,2007,1,107,W
Spread,2007,1,99,L
Bullet,2007,1,97,W
Nirvana,2007,1,94,L
Divorced,2007,1,80,W
Terror,2007,1,79,L
Hammer,2007,1,79,W
Biscuits,2007,1,79,L
SLM,2007,1,64,L
Nirvana,2007,2,142,W
Hammer,2007,2,127,W
NIO,2007,2,120,W
Terror,2007,2,105,L
Beer,2007,2,98,W
Bullet,2007,2,97,L
Spread,2007,2,95,L
SLM,2007,2,94,W
Divorced,2007,2,87,L
Biscuits,2007,2,85,L
Divorced,2007,3,121,W
Spread,2007,3,118,W
NIO,2007,3,106,W
Hammer,2007,3,105,W
SLM,2007,3,104,L
Beer,2007,3,94,L
Nirvana,2007,3,93,W
Bullet,2007,3,93,L
Biscuits,2007,3,91,L
Terror,2007,3,81,L
NIO,2008,1,110.22,W
SLM,2008,1,106.88,W
Biscuits,2008,1,91.78,W
Bullet,2008,1,90.94,W
Spread,2008,1,84.76,L
Terror,2008,1,80.34,W
Beer,2008,1,80.16,L
Hammer,2008,1,72,L
Divorced,2008,1,68.94,L
Nirvana,2008,1,66.88,L
Hammer,2008,2,109.78,W
Terror,2008,2,108.6,W
Biscuits,2008,2,107.44,L
SLM,2008,2,103.44,W
Bullet,2008,2,102.44,L
Divorced,2008,2,97.24,W
Nirvana,2008,2,94.24,W
Spread,2008,2,92.96,L
Beer,2008,2,77.94,L
NIO,2008,2,57.94,L
NIO,2008,3,125.74,W
Biscuits,2008,3,104.04,W
Hammer,2008,3,102.26,W
Spread,2008,3,100.26,W
Divorced,2008,3,91.08,W
Nirvana,2008,3,87.34,L
Beer,2008,3,73.06,L
Terror,2008,3,71.86,L
Bullet,2008,3,66,L
SLM,2008,3,59.94,L
];

[rubenmarin · Accepted solution]

Hi, as the question is static you can get a little help from script, flagging records that meets the conditions, ie:
tmpRank:
LOAD Team,Year,Week,Pts,Result,
If(Peek(Year)=Year and Peek(Week)=Week, Peek(Rank)+1, 1) as Rank
Resident GameResults
Order By Year, Week, Pts desc;

Left join (GameResults)
LOAD Team,Year,Week,If(Rank<=5 and Result='L', 1) as flag
Resident tmpRank;

DROP table tmpRank;

Then you can create a table with "Team" as dimension and "Sum(flag)" as expression

[jzimolong]

After spending more time with the expression, I was able to come up with a solution.  

Used the following expression in the straight-table chart:

sum({<Result={'L'}>}aggr(IF(RANK(sum(aggr(sum(Pts),Year,Week,Team)))<=5,1,0),Year,Week,Team))

Took a while to build this out, but I started at the base and built it out one step at a time.  Posting this in case anyone else may find it useful for a similar scenario.

However, rubenmarin 's reply, suggesting that the flag for "Top 5 Loss" be built in the LOAD SCRIPT, also works and actually creates a new field that can be used to filter down to the actual detail that makes up the flag.  Using the script to do heavy lifting is probably the better approach in general!

[jzimolong]

Thank you Rubenmarin, this worked out well!  I also like having a flag field to drill-down into detail.  Appreciate the solution.