Find total number of accounts and sort by it

[cognos05]

Hi ,

I have Sales Rep and there are N number of accounts under each sales rep. so Now i will be having a cross tab with

sales rep in rows and accounts nested. the measure would be revenue and columns would be YTD.

My requirement is I have to see their performance with respect to number of accounts they have worked for.

Say salesrep 1 would have revenue of 10 k but with only 5 accounts, but sales rep 2 would have a revenue of 30 k but with 20 accounts.

So I have to show the total number of accounts under each sales rep, and allow the user to sort on the total number of accounts they have worked on.

How do I add the number of accounts fields with value 1 for each account present and sort on the total.
I am expecting something like below.

                         Current YTD   Previous YTD   Number of Accounts
Sales Rep1   Account1   123             123                  1
                        Account2   345             234                  1
                   Account3   333              222                   1
                              801              579                   3
            
SalesRep2   Account5   123              111   1
                   Account6   124               112   1
                   Account7   125                113   1
                   Account8   126                 114   1
                   Account9   127                 115   1
                 Account10   128                116   1
                 Account11   129                117   1
                 Account12   130                118   1
                 Account13   131                119   1
                             1143                 1035  9

Now I should sort by total number of accounts worked by sales rep. I have added a query item for total number of accounts and what will be my expression.
Thanks,

Nithya

[Manu0521]

Try using order() function or create a tuple and then use this as a sort key.

[cognos05]

I came up to this point. I just used the value 1 for the Account Count- or conditon like if ytd is not zero then account count is 1.

Revnue-Measure              Ytd         PYTD          AccountCount
Dealer 1              Customer 1   123          123             1
                      Customer 2   1212   121             1
                            Total   1335   244            2
Now I am not able to sort on Dealers for whom the total Accountcount in Descending.

I tried to create a tuple(total,AccountCount) but total is a data item and it doesnt allows me And I am not able to use layout sorting.. Rather order function is also not working.

I ideally need to sort on Summary value of Account count for each distributor.

Any help on this sort issue is appreciated.

Thanks,
Nithya

[teresa.danna@gmail.com]

Maybe try first creating an object that would count([Account] for [dealer]) how many accounts each dealer has. Then do another object that would Rank([Dealer_Count]) the dealers. Once you have that info you could use the order() function to sort based on the dealer rank.

[cognos05]

Teresa,

Currently I hard code 1 as the value for my Account Count.

As you told here account count refers to number of customers under the dealer.So If I wanted to show this in my cross tab, I will create a calculated measure whose expression would be count(customer set for Dealer) but this doesnt allow me since count() function accepts only value.if i have 5 customers all my my 5 customers will have account count as 5 .

Is there any other way to sort with current approach.

Thanks,
Nithya

[cognos05]

I am also trying to use Count( customermemberset  for Dealer) as calaculated measure , so that If i have five customer under a dealer , this column will have 5 value in all 5 rows for this dealer.

but I am not able to use this because count is expecting value .
if I use count(value within set dealer) it gives same value for all dealers.

[Lynn]

Is your source relational or dimensional? The approach is going to vary depending on that answer. If you review the Forum Etiquette post you will see that it is advised to mention what your source is.

[cognos05]

Sorry about that Lynn, i forgot to mention the source. its a dimensional Source and i have explained my issue in the attached screen shot with sample example.

I appreciate your help.

Thanks,
Nithya

[MFGF]

Sorry about that Lynn, i forgot to mention the source. its a dimensional Source and i have explained my issue in the attached screen shot with sample example.

I appreciate your help.

Thanks,
Nithya

Ok. Your challenge seems to be in getting the expression for the number of accounts per rep?

Try this expression:

count([Revenue-Measure] within set siblings(currentMember([Your Rep and Account hierarchy])))

Once you have this, sorting should be the easy part :)

MF.

[cognos05]

Hi MFGF,

That gives me the account count, but if add a total summary item below my Accounts for each rep .

It totals all the columns, I wanted accountscount columns total to be same as its value. say if there are 5 accounts then total should be 5 and not 25 and I have a percentage column whose value needs to be averaged.

Is there a way, i can resolve this.

Thanks,
Nithya

[cognos05]

Hi MFGF,

I tried to add a total summary for each rep , so that it sums the values for each rep wrt to the the accounts.

But for some reason my report is not running and the loading circle keeps on rotating.

If I remove my calculated measure (Accounts for each rep ) from my report then i get the output.
My expression for that measure is Count([Measure] within set siblings(currentMember(Hierarchy)))

                                          YTD   PYTD  Accounts for each rep
Sales Rep 1   Customer Account1   1   2        4
                   Customer Account2   1   2        4
                   Customer Account3   1   2        4
                   Customer Account4   1   2        4
                                        Total    4   8         4
If add a Total summary how will it calculate the value for  Accounts for each rep , its expression being  Count([YTD ] within set siblings(currentMember(Hierarchy)))

Is this causing a error , my report specification says valid.but no output. and my view tabular data for the query gives me an error "WITHIN DETAIL summaries are not supported in list reports."once add my total  below the accounts for each rep.

Please let me know if I am doing something wrong.

Thanks,
Nithya

[MFGF]

Hi MFGF,

That gives me the account count, but if add a total summary item below my Accounts for each rep .

It totals all the columns, I wanted accountscount columns total to be same as its value. say if there are 5 accounts then total should be 5 and not 25 and I have a percentage column whose value needs to be averaged.

Is there a way, i can resolve this.

Thanks,
Nithya

This makes no sense. Why would you try to total these for each rep when the number already represents the accounts for each rep? I can't understand what you are trying to achieve by doing this? You said you wanted to sort the reps based on the number of accounts for each rep. You have this number - why not just select the rep row headings and sort them based on this existing value?

I'm confused...

MF.

[cognos05]

Hi MFGF,

Say there are 5 accounts under 1 rep, I would like to see how much revenue this rep generated for the 5 accounts , some reps may have less accounts and generated less revenue and some may have more accounts and generated more revenue. So I have to have a the total revenue for that rep ,along with the no of accounts he has worked on.

The revenue of sales rep was 4 dollars for the YTD and he has worked with 4 accounts.

Does it makes sense.Is there a way to achieve this

                                          YTD   PYTD  Accounts for each rep
Sales Rep 1   Customer Account1   1   2        4
                   Customer Account2   1   2        4
                   Customer Account3   1   2        4
                   Customer Account4   1   2        4
                                        Total    4   8         4

Or what I should go for is the second approach like just putting "1"with respect to each accounts and then use a total summary, here the problem I had was I was not able to sort on SalesRep with respect to total summary row and Accounts for each rep.

Sales Rep 1   Customer Account1   1   2        1
                   Customer Account2   1   2        1
                   Customer Account3   1   2        1
                   Customer Account4   1   2        1
                                        Total    4   8         4

Thanks,
Nithya 

[Manu0521]

Did you try to use function like order(Sales Rep,tuple(total,Accounts for each rep,Desc)
or
you can use the second approach and do layout sorting on by creating a tuple for total ,Accounts for each rep) and sort on this field.

Thanks,