Working with aggregate calculated fields examples
- Access the Query Builder, and click . This option only displays if you are logged on as Admin.
- Click .
Count of Open Tickets for Account
- Properties tab – specify the following:
- Name: OpenTickets
- Alias: OpenTickets
- Base Table: Account
- Calculation Type: Numeric
- Description: A count of open tickets per account
- Calculation tab
- In the top box, scroll to and expand the Ticket(Accountid->Accountid) [left] join
- Scroll to and click TICKETID.
{ACCOUNT.TICKET.TICKET NUMBER} is added to the calculation box.
- Click the Aggregate drop-down arrow and select Count.
- Conditions tab
- Option 1: Condition based on the Status field
Ticket.StatusCode = 'k6UJ9A000039
- Option 2: Condition based on the CompletedDate field
Ticket.CompletedDate is Null
- Option 1: Condition based on the Status field
Maximum Sales Potential per Account
- Properties tab – specify the following:
- Name: MaxSalesPotential
- Alias: MaximumSalesPotential
- Base Table: Account
- Calculation Type: Numeric
- Description: The highest sales potential of open opportunities for the account
- Calculation tab
- In the top box, scroll to and expand the Opportunity(Accountid->Accountid) [inner] join
- Scroll to and click SALESPOTENTIAL.
{ACCOUNT.OPPORTUNITY.SALESPOTENTIAL} is added to the calculation box.
- Click the Aggregate drop-down arrow and select Max.
- Conditions tab
- Option 1: Condition based on the Status field
Opportunity.Status = 'Open'
- Option 2: Condition based on the Closed field
Opportunity.Closed <> 'T'
- Option 3: Condition based on the ActualClose field
Opportunity.ActualClose is NULL
- Option 1: Condition based on the Status field
Minimum Sales Potential per Account
- Properties tab – specify the following:
- Name: MinSalesPotential
- Alias: MinimumSalesPotential
- Base Table: Account
- Calculation Type: Numeric
- Description: The lowest sales potential of open opportunities for the account
- Calculation tab
- In the top box, scroll to and expand the Opportunity(Accountid->Accountid) [inner] join
- Scroll to and click SALESPOTENTIAL.
{ACCOUNT.OPPORTUNITY.SALESPOTENTIAL} is added to the calculation box.
- Click the Aggregate drop-down arrow and select Min.
- Conditions tab
- Option 1: Condition based on the Status field
Opportunity.Status = 'Open'
- Option 2: Condition based on the Closed field
Opportunity.Closed <> 'T'
- Option 3: Condition based on the ActualClose field
Opportunity.ActualClose is NULL
- Option 1: Condition based on the Status field
Sum of Actual Amount
- Properties tab – specify the following:
- Name: SumActualAmount
- Alias: SumActualAmount
- Base Table: Account
- Calculation Type: Numeric
- Description: The total actual amount of won opportunities for the account
- Calculation tab
- In the top box, scroll to and expand the Opportunity(Accountid->Accountid) [inner] join.
- Scroll to and click ACTUALAMOUNT
{ACCOUNT.OPPORTUNITY.ACTUALAMOUNT} is added to the calculation box.
- Click the Aggregate drop-down arrow and select Sum.
- Conditions tab
- Option 1: Condition based on the Status field
Opportunity.Status = 'Closed - Won
- Option 2: Condition based on the CloseProbability
field
Opportunity.CloseProbability = '100'
- Option 3: Condition based on the ActualAmount field
Opportunity.ActualAmount is not NULL
- Option 1: Condition based on the Status field
Average Sales Potential of Open Opportunities
- Properties tab – specify the following:
- Name: AverageSalesPotential
- Alias: AvgSalesPotential
- Base Table: Account
- Calculation Type: Numeric
- Description: The average sales potential for open opportunities for the account
- Calculation tab
- In the top box, scroll to and expand the Opportunity(Accountid->Accountid) [left] join
- Scroll to and click SALESPOTENTIAL.
{ACCOUNT.OPPORTUNITY.SALESPOTENTIAL} is added to the calculation box.
- Click the Aggregate drop-down arrow and select Average.
- Conditions tab
- Option 1: Condition based on the Status field
Opportunity.Status = 'Open'
- Option 2: Condition based on the Closed field
Opportunity.Closed <> 'T'
- Option 3: Condition based on the ActualClose field
Opportunity.ActualClose is NULL
- Option 1: Condition based on the Status field