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
Try using order() function or create a tuple and then use this as a sort key.
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
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.
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
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.
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.
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
Quote from: nithya1224 on 30 Apr 2014 08:22:26 AM
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.
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
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
Quote from: nithya1224 on 30 Apr 2014 01:42:03 PM
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.
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
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,