Singleton to show percentage

[bpothier]

I may not be going down the right path but here it goes.

I have a list with employee name, account, sales cycle length, revenue. This works fine. (sales cycle length is  calculated as follows if case when status = won then difference between open and clsoe date) I am trying to added average sales cycle length to it. When I add a singleton with my calculation (total won opportunities/total opportunities) I get the overall Percentage for the team and not limited by rep.

Is there a way to show only the reps overall sales cycle length? I have tried adding by [Employee] after the total won opportunities/total opportunities but it throws errors

Any ideas?

[MFGF]

"by [Employee]"? Do you mean "for [Employee]"?

MF.

[bpothier]

I have tried both

[MFGF]

Are you putting the singleton inside your list? If you just want the average sales cycle length for each employee, you could just add the calculation directly to your list as a new column rather than as a singleton. Or is it more complex than this?

Cheers!

MF.

[bpothier]

I may be making it more complicated. I have a filter on the data coming in to only be 'Won' right now because I can't seem to get supression to work. When I set supression is supresses on the revenue column and not the sales length column. I f I could get that to work I am pretty sure I could get the average in there just fine

[MFGF]

Hi,

I'm not quite clear on what you are trying to do when you refer to suppression. Can you explain in a little more detail? It might be something simple?

Cheers!

MF.

[bpothier]

sure - thanks for taking the time

here is how it looks right now

filters are by date and by manager

Sales Cycle length looks like this
case when [Presentation Layer].[Opty with Opty Prods].[Opty Status Code] = 'Won' then _days_between([Presentation Layer].[Opty with Opty Prods].[Closed Date],[Presentation Layer].[Opty with Opty Prods].[Created Date]) else 0 end

I added pics of what it looks like with and woitout supression. When I add supression it looks like it removes the 0 revenue and I want it to remove the 0 sales length

[MFGF]

Do you get the desired result if you add a detail filter (after aggregation) with an expression of [Sales Cycle length] <> 0

Would it be easier just to filter on [Presentation Layer].[Opty with Opty Prods].[Opty Status Code] = 'Won' or are you doing other things that would make that impossible?

MF.

[bpothier]

I had a filter on just 'Won' but got stuck trying to get the total average. I attached what it looks like now. Everything is good except I am sort of back at the beginngin where the average is showing 1 for each  - taking the average of each opty instead of each rep.

For closed rate I used the following for each potential status (Won, Lost, Expired etc:
if([Presentation Layer].[Opty with Opty Prods].[Opty Status Code] = 'Won')
then (1)
else (0)

Then totalled then like [Won] + [Lost]} etc

The actual Close rate field looks like this
[count Won]/[Total]

when I add for [Employee Name] to the Close Rate field it says:
QE-DEF-0459 CCLException
QE-DEF-0260
QE-DEF-0261 and point to the word for

[MFGF]

So are you dealing with more than just "Won" scenarios? The fact that you are trying to total "Won" and "Lost" seems to indicate you have a second calc similar to "Won" to determine the measure value for "Lost"? If so, you can't filter on "Won" as I suggested.

I'm still struggling a little to understand your requirement. You don't want to see Sales Cycle lengths that are zero - how are these determined for "Lost" / "Expired" etc? Will they be zero for these situations or will the lengths be real values?

Sorry - my old muppety brain must be getting really senile :)

MF.

[bpothier]

sorry for the confusion. The intial report was showing 'Won' opties and the sales cycle length and revenue associated with them. The reason why I do not want to see 0 sales cycle lengths is because that means the sales cycle is still open or a staus other than 'Won'.

So basically the report shows the details and sales cycle length of the won opties but I thougth it would be cool to show their overall close rate which takes into account all the statuses and not just 'Won'.

As it is now it is showing the closure rate of 1 for each opportunity because technically it is closed. Trying to get the calculation for overall close rate into the report somehow by rep so instead of the way it is now.

Currently looks like:
rep       close rate      duns      account    opty name     sales cycle length    revenue
joe           1                12345     ibm             opty 1                29                      $50
joe           1                 12346    google         opty 2                13                     $60

woudl like it to be:
rep        close rate        duns   account      opty name       sales cycle length      revenue
joe            68              12345      ibm             opty 1                     29                      $50
                                   12346      google        opty 2                       13                    $60

Hope I did not make it worse  :)