COGNOiSe.com - The IBM Cognos Community

Hi Cognoise friends  :)
I have an interesting puzzle in Cognos Report Studio but I dont know how to resolve it, so I need some suggestions/views/opinnions from you.

I need to categorize the following 'SalesPerson' dataitem into ClassA, ClassB and ClassC based on the following rule:

In last month's record,
1. If a customer is served by only 1 SalesPerson then ClassA
2. If a customer is served by multiple SalesPerson then SalesPerson who served that customer majority of the time is ClassB
3. If a customer is served by multiple SalesPerson & those SalesPerson have served that customer equal time as other SalesPerson then the last one to serve is ClassC

(Note: Priority is based on ascending order of the above rules)

Sample records are:
Customer     SalesPerson     ServedDate
C1                     S1                    jan 1
C1                     S1                    jan 2
C1                     S1                    jan 3

C2                     S2                    jan 4
C2                     S2                    jan 5
C2                     S3                    jan 6

C3                     S4                    jan 7
C3                     S4                    jan 8
C3                     S5                    jan 9
C3                     S5                    jan 10


Desired Output in List Report:
Customer            SalesPerson         Class
C1                             S1                   ClassA
C2                             S2                   ClassB
C3                             S5                   ClassC


The original requirement is more complex with additional rules for ClassD, ClassE, ClassF etc. but the initial logic with the above puzzle should give me idea on how to tackle the rest.

Thanks a lot!

Slim

Hi,

you can concatenate CustomerName with Salesperson and then take a count of this string

If its greater than 1 then Class b
If =1 then class A and so on.

Regards
saumil

Thank you Saumil for your suggestion, It would be more helpful if you could be more descriptive.

As you suggested, I could take distinct count of the concatenated string and classify into Class A and Class B but how about Class C rule when S4 and S5 have the same count and 'ServedDate' needs to be considered into the logic ?

And how do I display the output in List report as depicted in 'Desired Output in List Report' ?

Any suggestion would be really appreciated.
Many Thanks :)

Slim

Hi,
You have to create a dataItem named class which contains all the logic for classA, b,c and so on.
You can create a data item to get displayed in list report and if you get the same count for s4 and s5. you need to take other columns which can make distinct for  2 salespeople
Or might be you have to write a customized query using case/if-else construct.



Regards
saumil

Hi,

How to find when count of s4 for c1 = count of s5 for c1 ?

thanks,
Slim

This is a relational model I assume?

I'm thinking a scope clause on a count function and some julian date conversion on the max served date might get you to a mathematical way to find the answer.

Not entirely sure, but try counting salesperson for customer and multiply by 10,000,000 and then add the julian value of the maximum date for customer and salesperson and add that value to the count.

Today's Julian date value is 2,456,708 so multiplying your count by 10,000,000 gives weight to this such that anyone serving more frequently (class b) will win regardless of max date whereas a tie in number of times served will allow the julian date value to be the deciding factor.