Hi All,
I have my distributors and sales reps nested in my rows and I have my product level in my column . Now I will add a dataitem named rank in the column which would show the rep ranking based on their total quantity sold.
ProductLevel( Product A,b,c,d)
ProductA ProductB ProductC ProductD Rank
A Abc 12 43 55 33 (143) 3
Bcd 13 35 65 33 (146) 2
Dac 13 34 55 234 (336) 1
Cas 14 34 12 34 (94) 4
Also if the total value of product level is same for the reps ,then rank will be same for both...
Please let me know what kind of expression should i use in the rank data item.
Thanks,
Nithya
Hi Nithuya,
Are Products A - D members of a set?
if yes, then :
rank
(
currentMeasure within set [set that provides Products A-D]
)
if you've got some funky filtering going on in your query, you may want to try within detail:
rank
(
currentMeasure within detail [set that provides Products A-D]
)
Products A to D are members and I have used set(A,b,c,d).
Now the rank is for the sales rep under a distributor on the total of these product quantitites.
My result for the rank expression is blank.
I have two levels nested in row from a dimension, Do I need to use current member function and tuple.
Thanks,
Nithya
Yes, that's right I think.
Thanks MDX Expressor .
But how do I use tuple here, whether it should be inside rank().
CurrentMember(Row Hierarchy),Qty and what about columns.
column is a set with different members .
Thanks,
Nithya
Hi Nithya,
I think you can be more elegant than this in your rank statement, but I want you to understand the mechanics...
You have your rank in the columns. That means you want to rank a set of rows (distributors) based on a measure, that's what a rank does. In your case, you need a custom measure. One that is isolated through an intersection in the cube. If Products A-D make up all the siblings of a parent, it probably easier to do this through a parent tuple, but I'll assume you have a custom set to create Products A-D.
To create your custom measure:
aggregate([specify the rank measure(not currentMeasure)] within set [Set of Products])
So, I'll assume you're measure is [Sales]:
rank(
aggregate ([Sales] within set [Products A - D])
within set [Distributors]
)
Now, I'd probably break out the custom Measure into it's own data item, and then call that new data item in this calculation (replacing the aggregate statement), but hopefully you get the idea.
Hi MdxExpressor,
Thanks for the reply.
I have used the below expression on my rank data item
rank (aggregate([Full Revenue] within set([ProductGroup])) for [Distributors])
ProductGroup is my custom set in columns having all 4 products.
Distributors is the first level in row to which reps are nested.
Now the ranking works, but I am not able to sort the reps with in distributors.(both distributors and reps are levels from same dimension)
Sorting doesnt work.
I tried sorting the reps level ,on rank data item but it throws an error like
Found an internal error: '!mapSuccess - reportName=ranking reps - edgeName=_edge - edgeOrdinal=0
Advanced sorting and order() is also not working on these data items
Any guess on what could be the issue.
Thanks,
Nithya
Are Distributros and Reps different levels of the same hierarchy?
yes ,,they are different levels from same hierarchy.
Maybe 2 rank columns? One to rank distributors, one to rank reps? Then edit your layout sorting to sort one then the other?
If I rank rep then its fine. But sorting on rep is not working.
When I set sorting to the rep level and choose rank column ,its throwing an error while running the report.
Found an internal error: '!mapSuccess - reportName=ranking reps - edgeName=_edge - edgeOrdinal=0
What could be the reason...
Thanks,
Nithya
If I use set instead of levels it sorts properly..but I need to have levels in my rows.
I used descendants expression in my level and then sorted and it worked,,not sure why it throws error for a level directly..
Thanks for the help.