cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

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 .

9 Replies
Anonymous
Not applicable
Author

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)

Master II

Hi,

Try this,

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

Anonymous
Not applicable
Author

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

Master II

Hi,

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

it is much easier:

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

MVP

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)

Community Browser