Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jzimolong
Creator II
Creator II

Count Occurrences in Top N with Second Condition

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

Result.png

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

Table.png

 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
];

 

1 Solution

Accepted Solutions
rubenmarin

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

View solution in original post

3 Replies
rubenmarin

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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