COGNOiSe.com - The IBM Cognos Community

Hi,

I am having a crosstab with reps in rows and certain products in column with sales as measure
           Product1    Product2      Product3     Product4  Product5
A1        12                34               93                 53          87
A2        14                23               14                 17          76
A3        56                33               22                 33          12   
A4        08                91               21                 11          11   

from this information I wanted to build a report which shows the fixed products and the reps who had max sales on each of these product and their respective sales qty.
as shown below.

Product1    Rep Name     Sales Value
Product2    RepName      Sales Value
Product3    RepName      SalesValue
Product4    RepName      SalesValue
Product5    RepName      SalesValue

I am not sure whether to use crosstab or list to get the above information.
And function to get the repname who has the max sales on each product.

Thanks,
Nithya

Quote from: nithya1224 on 02 Feb 2015 08:03:36 AM
Hi,

I am having a crosstab with reps in rows and certain products in column with sales as measure
           Product1    Product2      Product3     Product4  Product5
A1        12                34               93                 53          87
A2        14                23               14                 17          76
A3        56                33               22                 33          12   
A4        08                91               21                 11          11   

from this information I wanted to build a report which shows the fixed products and the reps who had max sales on each of these product and their respective sales qty.
as shown below.

Product1    Rep Name     Sales Value
Product2    RepName      Sales Value
Product3    RepName      SalesValue
Product4    RepName      SalesValue
Product5    RepName      SalesValue

I am not sure whether to use crosstab or list to get the above information.
And function to get the repname who has the max sales on each product.

Thanks,
Nithya

That looks like a list to me :)

You can use the topCount() function to get the top rep based on sales value for each product

Cheers!

MF.

Hi MFGF,

I used a data item1 and the expression was
topCount(Reps,1,tuple([ProductA],[Qty]))  and it worked.

this will give me the rep with maximum sale on say only  product A

this is my first column and I will place the Measure  column next to it in the list.I also wanted to show for which product.

So Should I create 5 dataitems like this for 5 different products, Where will I display my products

Thanks,
Nithya

I solved it.
I used a set expression for the products .

Set(Product A, Product B, ProductC)

Then used this data item in

topCount(reps,1,tuple(currentMember(Product),[Qty]))

Thanks for your help.

I was thinking of Maximum function.

Thanks,
Nithya

Quote from: nithya1224 on 02 Feb 2015 09:23:46 AM
I solved it.
I used a set expression for the products .

Set(Product A, Product B, ProductC)

Then used this data item in

topCount(reps,1,tuple(currentMember(Product),[Qty]))

Thanks for your help.

I was thinking of Maximum function.

Thanks,
Nithya

Hi,

You don't need the set - you can use the Products hierarchy in the currentMember funtion.

Cheers!

MF.

Hi MFGF,

Got in to one issue there are tow reps with same Sales Qty, In this case I need two of the reps name with comma seperator. Is this possible ?

Thanks,
Nithya

Quote from: nithya1224 on 04 Feb 2015 01:46:22 PM
Hi MFGF,

Got in to one issue there are tow reps with same Sales Qty, In this case I need two of the reps name with comma seperator. Is this possible ?

Thanks,
Nithya

Hi,

No - topCount will return the top <n> members, but doesn't treat two members with the same measure value as being the same. All of the top 10 could in theory have the same value, and you would simply see this set of members.

MF.

So the requirement is to getthe top sales person from each product and if there are two sales person with sames sales then bring in both of them.

How else can I achieve this.

Thanks,
Nithya

Hi MFGF,

Was trying the below code with rank function to achieve the same.

Filter(reps, rank([Product] within set [reps])<=1)

Here I can specify one static product or and product from hierarchy..CurrentMember(Products).

Say If I have five products in a list and then the second column as rep with above expression for data item. I am always getting only one rep,I should get 5 reps for the five products right?

List

ColumnA - product  -  Expression - set(A,B,C,D,E)
Column B- Reps with maximum sales for the corresponding products - Expression - Filter(reps, rank(currentMember([ProductHierarchy]) within set [reps])<=1)

Thanks,
Nithya