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

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
Highlighted

Re: Count Occurrences in Top N with Second Condition

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
Highlighted

Re: Count Occurrences in Top N with Second Condition

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

jzimolong
Contributor II

Re: Count Occurrences in Top N with Second Condition

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!

 

Highlighted
jzimolong
Contributor II

Re: Count Occurrences in Top N with Second Condition

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