A customer saved search with the following results is easily done using a simple CASE SQL formula:
Status  Total Customers  Territory X  Territory Y  Territory Z 
CUSTOMERClosed Won  97  60  22  15 
LEADUndecided  9  4  2  3 
PROSPECTProposal  2  1  1  0 
PROSPECTQualified  12  3  4  5 
Total  120  87  23  10 
To insert columns for a perline % of Total that gives the below report, a more advanced SQL formula is needed.
Status  Total Customers  Territory X  Territory X  Territory Y  Territory Y  Territory Z  Territory Z 
% of Total  % of Total  % of Total  
CUSTOMERClosed Won  97  60  62%  22  23%  15  15% 
LEADUndecided  9  4  44%  2  22%  3  33% 
PROSPECTProposal  2  1  50%  1  50%  0  0% 
PROSPECTQualified  12  3  25%  4  33%  5  42% 
Total  120  87  73%  23  19%  10  8% 
To compute for the perline % of Total, use the following formula:
Field  Summary Type  Function  Formula 
Formula (Percent)  Maximum  Round to Hundredths  sum(Group Formula)/ nullif(count({internalid}),0) 
To create the sample report:
1. Navigate to Lists > Search > Saved Searches > New.
2. Select Customer.
3. Provide a search title.
4. Under Criteria tab, set the desired filters.
5. Under Results tab, add the following:
Field  Summary Type  Function  Formula 
Status  Group 


Internal ID  Count 


Formula (Numeric)  Sum 
 TO_NUMBER(CASE WHEN ({territory}= 'X') THEN 1 ELSE 0 END) 
Formula(Percent)  Maximum  Round to Hundredths  SUM(TO_NUMBER(CASE WHEN ({territory}= 'X') THEN 1 ELSE 0 END))/ NULLIF(COUNT({internalid}),0) 
Formula (Numeric)  Sum 
 TO_NUMBER(CASE WHEN ({territory}= 'Y') THEN 1 ELSE 0 END) 
Formula(Percent)  Maximum  Round to Hundredths  SUM(TO_NUMBER(CASE WHEN ({territory}= 'Y') THEN 1 ELSE 0 END))/ NULLIF(COUNT({internalid}),0) 
Formula (Numeric)  Sum 
 TO_NUMBER(CASE WHEN ({territory}= 'Z') THEN 1 ELSE 0 END) 
Formula(Percent)  Maximum  Round to Hundredths  SUM(TO_NUMBER(CASE WHEN ({territory}= 'Z') THEN 1 ELSE 0 END))/ NULLIF(COUNT({internalid}),0) 
6. Click Save & Run.
No comments:
Post a Comment