Chart to display "Top 5 / Bottom 5" values

[jzimolong]

I've been trying to create a chart that displays only the Top 5 and Bottom 5 values for a given dimension.  I've attached the source table (Excel), which has following columns:

• HistoryYear
• HistoryWeek
• HistoryTeam
• HistoryPtsFor

The Excel file also shows, by color, the actual rows that I want to ultimately display in the chart (straight table):

• Rows highlighted in Yellow = Top 5 "HistoryPtsFor" for each "HistoryWeek"
• Rows highlighted in Orange = Bottom 5 "HistoryPtsFor" for each "HistoryWeek"
• All other rows would not appear in the chart
• All columns also display in the chart

I've tried combinations of the Aggr/Rank expressions, dimension limits and several others with no success.  Any guidance would be greatly appreciated.  Thought this would be fairly straight-forward, but I'm struggling with it.  It seems like an expression using Aggr is the way to go, in tandem with Rank.

I'm using Qlikview Personal Edition.

Regards,

Joe

[chrismarlow]

Hi,

Adapting from the thread here AGGR and Rank over Multiple dimension - Qlik Community - 96014

if(Aggr(Rank(TOTAL Sum(HistoryPtsFor)),HistoryYear,HistoryWeek,HistoryTeam)<=5
OR Aggr(Rank(TOTAL -Sum(HistoryPtsFor)),HistoryYear,HistoryWeek,HistoryTeam)<=5,Sum(HistoryPtsFor),Null())

Looks like it might be a start;

Cheers,

Chris.

EDIT - Just spotted you have a grouping on HistoryWeek ... hmmm.

[jzimolong]

Thanks Chris, it's pretty close!  When I add a list box for HistoryWeek, then select a specific week it works:

I just need to figure out how to tweak your expressions to do that extra level to HistoryWeek as you noted in your edit.  I'll keep at it, and thanks for the lead.

[chrismarlow]

Hi,

After some hacking around you could try this as a starting point, but is a bit messy as relies on creating a calculated dimesion with the 2 you are grouping over (you could tidy this up by adding Only() functions and hiding/moving columns a bit), so maybe someone has a better solution;

Cheers,

Chris.

[chrismarlow · Accepted solution]

Hi,

Third hack after walking away and coming back. I think this works better. Key penny dropping for me is there seem to be 2 ways Qlik uses TOTAL, one in Rank is all/nothing & makes you dependent on where your dimensions are and how they are structured (so the need to append together in a calculated dimension in my second hack).

However max/min use TOTAL with field qualifiers, that means I think you get a more robust solution;

If(sum(HistoryPtsFor)>=max(TOTAL <HistoryWeek> Aggr(sum(HistoryPtsFor),HistoryTeam,HistoryWeek,HistoryYear),5)
OR sum(HistoryPtsFor)<=min(TOTAL <HistoryWeek> Aggr(sum(HistoryPtsFor),HistoryTeam,HistoryWeek,HistoryYear),5),sum(HistoryPtsFor))

Cheers,

Chris.

[jzimolong]

Chris, that worked perfectly!  Thank you for taking the time to work through this, greatly appreciated!