+ Start a Discussion
ExecbizExecbiz 

Reporting - sort by intervals

I have a custom formula field in the opportunity record, "Create-to-Close Interval," that simply outputs the number of days between the creation of an opportunity and its close date.  I would like to run a report that groups all my opps into Create-to-Close intervals of 0-30, 31-60, etc.  I figured that option would be in the Select Grouping window, but when I choose to sort by that field, I cannot change the "Group by" option.

 

How can I change the grouping intervals in the sort options?

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
snugglessnuggles
i think your best bet is to create a second formula field with a case statement that assigns one of those grouping values to the record, then grouping by that.

All Answers

snugglessnuggles
i think your best bet is to create a second formula field with a case statement that assigns one of those grouping values to the record, then grouping by that.
This was selected as the best answer
ExecbizExecbiz
 Works great, thanks!
pecrow23pecrow23

Execbiz,

 

Would you mind sharing the formulas you used to create both custom fields?

 

Thanks! 

ExecbizExecbiz

Sure thing.  One note, I actually changed the structure of mine so that it was actually a create-to-invoicing interval...same idea, different execution.  You can modify it easily to go back to create-to-close.

 

 

IF(ISNULL(Invoice_Sent__c), null, (Invoice_Sent__c - DATEVALUE(CreatedDate)))

 

 And here's the "interval category" field I created so I could report by intervals, in my case 30 day intervals.  As you can see, it sets the case as 1, then returns either 1 or 0 based on an if statement.  The return of 1 kicks back the resultant category string:

 

CASE( 1, IF (Create_to_Close_Interval__c < 31,1,0), "0-30 Days",

IF (Create_to_Close_Interval__c < 61,1,0), "31-60",

IF (Create_to_Close_Interval__c < 91,1,0), "61-90",

IF (Create_to_Close_Interval__c < 121,1,0), "91-120",

"Other")

 

Good luck

 

Message Edited by Execbiz on 08-04-2009 05:51 PM
pecrow23pecrow23

Thanks!  That worked perfectly.  I appreciate your quick response and help!  Much appreciated!