8 Replies Latest reply: Oct 16, 2014 1:03 PM by Steve Taylor RSS

    Top N by Dimension using Set Analysis in Expression

    Steve Taylor

      I have a need to show top 25 products by Sub Department in my company.

       

      I've seen several descriptions and solutions to this problem, Oleg Troyansky has an excellent write-up on this topic where he shows solutions using Dimension Limiting in Straight tables, Calculated Dimensions in Straight/Pivot tables and expression limiting using set analysis in a Pivot table.

       

      If you haven't read this article, you should - it is an excellent read.

       

      However when he discusses he set analysis version he no longer breaks it out by dimension. Simply top 100 - or 1000 can't remember which overall.

       

      For my needs, assume we have 4 sub departments and I need to show top 25 products in each. This should yield 100 rows.

       

      For all my tinkering, I can only get it to show top 25 over the available set, broken down by sub department. That's 25 Total.

       

      I could probably solve the problem using a calculated dimension or two, but I'm stubborn and am looking for optimal performance. This is my expression (showing top 25 overall):

       

      sum({<MasterPLUCode={"=rank(sum(Amount*NetFlag),4)<=25"}>}Amount*NetFlag)

       

      Any help offered would be hugely appreciated.