Calculating Rank function in Cognos Dimensional package

[cognos05]

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

[MDXpressor]

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]
)

   

[cognos05]

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

[MDXpressor]

Yes, that's right I think.

[cognos05]

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

[MDXpressor]

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]:

Code Select Expand

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.

[cognos05]

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

[MDXpressor]

Are Distributros and Reps different levels of the same hierarchy?

[cognos05]

yes ,,they are different levels from same hierarchy.

[MDXpressor]

Maybe 2 rank columns?  One to rank distributors, one to rank reps?  Then edit your layout sorting to sort one then the other?

[cognos05]

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

[cognos05]

If I use set instead of levels it sorts properly..but I need to have levels in my rows.

[cognos05]

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.