Basically, I need to write a Crystal Reports formula that has many groupings in it to calculate a percentage close rate. Since I'm writing a report off the Production database and it includes many group dimensions, there are several formulas. I currently have the following formula:
If DistinctCount({OPPORTUNITY.OPPORTUNITYID},{@Close Date},"Quarterly") <> 0
Then (( Sum (
{@WinCount},
{@Close Date}, "quarterly") / DistinctCount({OPPORTUNITY.OPPORTUNITYID},{@Close Date},"Quarterly"))*100)
Else 0
My @CloseDate formula is:
if NOT(ISNULL({OPPORTUNITY.ACTUALCLOSE}))
then {OPPORTUNITY.ACTUALCLOSE}
My @Win Count formula just counts the number of wins at the opportunity close stage.
My Lead count is a formula that counts any opportunity not at the close stage.
Using these formulas, I get a 100% Close Rate. I need a date formula that will act "dynamically", so I will probably have to hard code something. For example, I may win a deal today (the actual close date), but I need to calculate and display in the previous quarter. Since I'm new to Crystal Reports, I'm looking for an easy approach.
The data needs to be displayed as follows:
Period # of Leads # of Wins Close Rate
2010 Q3 45 5 11%
Any ideas will be greatly appreciated.
IP Logged