how to get top 3 salesmanID's for sales,product wise using set analysis

[Anonymous]

i am trying to get product wise top 3 salesman ID's who had top sales in pivot table view . i was able to get this using rank( ) and Aggr() in calculated dimension if(Aggr(rank(sum(Sales)),[Product ID],[Salesman ID])<= 3,[Salesman ID]) and productID as another dimension ,i want to get the same result using set analysis,Is it possible to achieve the same by using only set_analysis ?

could any one help me with the set_analysis  expression !  i am no way near to solving this using set analysis 

i attached the Transact data file which is my source .

Thank you .

[Anonymous]

you could build a combined dimension like that:

autonumber([Product ID] & '|'& [Salesman ID]) as ProductSalesman

and then use expression search within set analysis:

sum({<

ProductSalesman={"=rank(sum(Sales),4)<=3"}

>} Sales)

[devarasu07]

Hi,

Try this,

=if(aggr(RANK(SUm(Sales),4),[Product ID],[Salesman ID])<=3,aggr(RANK(SUm(Sales),4),[Product ID],[Salesman ID]))

[Anonymous]

hi Devarasu the calculated dimension you gave is presenting the data more clearly than my dimension , i want the same to be achieved through set_analysis is it possible to do with out using aggr( ) ?

thank you for the reply 

[devarasu07]

Hi,

Is your issue resolved? if so help to close this thread. Tks

[Anonymous]

Hi

    like this i am getting when i use only set_analysis with out Aggr( ), My expressions are  sum({<[Product ID]={"=rank(Sum(Sales))<=3"}>}Sales) for product wise top 3 sales , and for salesmanID wise top 3 :

sum({<[Salesman ID]={"=rank(Sum(Sales))<=3"}>}Sales)

instead of getting individually i want both of them combined in one set analysis expression so they can give product wise top three salesmanID's is it possible ?                              

[Anonymous]

Hi   Robin Hausdorfer

   i tried with combined dimension but not completely successful , some values are missing but what ever the values i am got they are correct values ,

[Anonymous]

that's why you should build a combined dimension like

autonumber([Product ID] & '|'& [Salesman ID]) as ProductSalesman

You don't even need to put the ProductSalesman dimenson into your chart.

You can use Product ID and Salesman ID instead as dimensions

But the set analysis must be like that:

sum({<

ProductSalesman={"=rank(sum(Sales),4)<=3"}

>} Sales)

[Anonymous]

it is much easier:

But take into account, that the sorting is important...

[maxgro]

Set analysis is calculated once per chart, so I don't think you can us in this case: you want a set analysis calculated for  individual dimension values (product)