Displaying top 3 items based on two different level

[sthabinash]

I have a table with the fields as follows:

|Week| Department| Org_Location| Item | Sales|

I am currently trying to find the top 3 organization location based on the sales. And also the top 3 items sold at each location. I have successfully generated the top 3 organization locations based on sales. But I can't seem to generate the top 3 items sold at each location and display in the same table. Could anyone help me out.
Thanks.

[norkos]

Hi,

Are you working from a relational, a DMR or an OLAP model?
What do you mean about table?  A list or a crosstab item?

If you are working with DMR or OLAP model then the solution is easy. You just have to create a crosstab, insert the 4 dimensional items as nested rows and insert the sales measure as column.
You have to insert the top 3 elements as Set expression type Query calculations. The expressions of the Query calcs must look like these: TopCount([Org_Location], 3, [Sales]) and TopCount([Item], 3, [Sales]).

NoRkoS

[sthabinash]

I am using DMR. I am actually trying to create the report using the list. It seems that it is easily generated in the crosstab(thanx for the idea), but is there anyway from which I can generate the same result using list??

[norkos]

The IBM best practise recommends to create list from relational and crosstab from DMR or OLAP models.
In other cases you have to be very careful, and you have to follow this practise especially if you use dimensional functions.

So you shouldn't create list from DMR, because some function may not work properly...