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

# Top N by Dimension using Set Analysis in Expression

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.

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.

• ###### Re: Top N by Dimension using Set Analysis in Expression

Can you provide little data with sample solution in excel file?

• ###### Re: Top N by Dimension using Set Analysis in Expression

I've attached a hugely scaled down version of our data. Should be enough to illustrate my point.

• ###### Re: Top N by Dimension using Set Analysis in Expression

The Set Analysis in an expression is evaluated once for the whole chart, not for every dimension value seperately.

• ###### Re: Top N by Dimension using Set Analysis in Expression

So would you say that a calculated dimension is the only way to solve this problem?

I knew that limitation of set analysis going into this. My hope was that using Set Analysis, I could produce a set of the top 25 products for each sub department - so if I had 4 sub departments, 100 products in the set - then the pivot table could grab the relevant items for each sub department. Does that make sense and/or possible?

• ###### Re: Top N by Dimension using Set Analysis in Expression

As far as I know you need a calculated dimension, something like this example:

 =aggr( ceil( 25 * aggr( rank(sum(LineSalesAmount)) / COUNT(TOTAL DISTINCT ProductName) ,Year ,ProductName ) ) ,Year ,ProductName

)

• ###### Re: Top N by Dimension using Set Analysis in Expression

Hi

You can try the following expression for Net Sales. In this case you don't need a dimension.

SUM({<MasterPLUCode={"=AGGR(Rank(Sum(Amount*NetFlag),4), [Sub Department], MasterPLUCode)<=25"}>} Amount * NetFlag)

Cheers

Darius

• ###### Re: Top N by Dimension using Set Analysis in Expression

Darius,

I tried this last night and it didn't work, but that was against my full document. When tried against the sample qvw I provided, it worked like a charm. There must be an issue in my data model/load that I need to track down - which is good to know.

Awarding you for the answer as it was exactly what I was looking for. Just wish I'd tried it on the sample document before posting and saved myself a bunch of time

Thanks again!

• ###### Re: Top N by Dimension using Set Analysis in Expression

So I tracked down this problem. When I created the sample qvw, I only loaded our sales table, not the discounts or coupons tables.

When the consultant first created this application for us, they converted the PLUs in the sales table to integers, but not in the other tables which get concatenated into facts. This meant that my masterplus were only being linked to sale items and not all fact items.

This error has been in our application for over a year now and only found thanks to this exercise and the help of folks on this forum!