I have a two dimension (year and id code) bar chart with one expression (profit) and I would like to show top 5 id's by profit for every year. I manage to get the top 5 id codes by profit for every year by embedding rank() function in the expression and calculating the profit for only the top 5 for every year, but of course the ranking doesn't reflect in the legend, which shows all the id codes although their profit hasn't been calculated. I would like the legend to show only the top 5 id's for every year, how can i do that? I've tried to use the rank function for the id code dimension but that results in calc error and including the expression string in the sort by expression box of the id code dimension doesn't work either. Is it possible to suppress a dimension when expression is null?
The chart is based on a normal 2 dimension pivot table with one calculated expression. Normally if the calculated expression results in null, it can be suppressed in the presentation tab - e.g. =if(sum(profit)>100000,sum(profit)) per id and year , the table then shows only id's which fulfil this condition. However I'd like to rank the profits as top 5 per year and if i use the rank() function in the if condition string - .i.e =if(rank(sum(profit)>5,(sum(profit)) the suppress of missing or zero-values doesn't work anymore, although the results are valid. I use QV 10. Any ideas how to use rank and suppress zero/null values in a two dimension table/chart would be most welcome?
