Top N by Dimension using Set Analysis in Expression

[Anonymous]

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.

[MK_QSL]

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

[Anonymous]

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

[stigchel]

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

[Anonymous]

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?

[stigchel]

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

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

)

[d_pranskus · Accepted solution]

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

[Anonymous]

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!

[Anonymous]

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!

[Anonymous]

Hi Darius,

I tried applying your solution to my problem. I need to show the top 10 projects by region (Need to use expression, not calculated dimension)

Expression1:

Sum({<[Project Name]={"=AGGR(Rank(Sum({<FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount),4),Region,[Project Name])<=10"}>}Amount)/1000000

If I use the above expression then the projects are limited to 10 but the numbers aren't correct (image 1) since it's taking the total amount without considering the filters. However, if I use the expression below the numbers are correct but it's not limited to 10 projects any more (image 2). What am I doing wrong?

Expression 2:

Sum({<[Project Name]={"=AGGR(Rank(Sum({<FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount),1),Region,[Project Name])<=10"}>}{<FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount)/1000000