Friday, July 5, 2019

Display Per Line Percent of Total (% of Total) on Saved Searches


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

CUSTOMER-Closed Won

97

60

22

15

LEAD-Undecided

9

4

2

3

PROSPECT-Proposal

2

1

1

0

PROSPECT-Qualified

12

3

4

5

Total

120

87

23

10


To insert columns for a per-line % 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

CUSTOMER-Closed Won

97

60

62%

22

23%

15

15%

LEAD-Undecided

9

4

44%

2

22%

3

33%

PROSPECT-Proposal

2

1

50%

1

50%

0

0%

PROSPECT-Qualified

12

3

25%

4

33%

5

42%

Total

120

87

73%

23

19%

10

8%


To compute for the per-line % 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