Working with Aggregate Calculated Fields - Examples

Use the Calculated Field Condition tab to define and apply a SQL condition statement to further refine the data included in an aggregate calculated field. You cannot create an aggregate calculated field based on a calculated field.
To add calculated field:
  1. Open the Query Builder, and click Calculations. This button only appears if you are logged on as Admin.
  2. Click Add.

Count of Open Tickets for Account

  1. Properties tab – specify the following:
    1. Name: OpenTickets
    2. Alias: OpenTickets
    3. Base Table: Account
    4. Calculation Type: Numeric
    5. Description: A count of open tickets per account
  2. Calculation tab
    1. In the top box, scroll to and expand the Ticket(Accountid->Accountid) [left] join
    2. Scroll to and click TICKETID.

      {ACCOUNT.TICKET.TICKET NUMBER} is added to the calculation box.

    3. Click the Aggregate drop-down arrow and select Count.
  3. 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

Maximum Sales Potential per Account

  1. Properties tab – specify the following:
    1. Name: MaxSalesPotential
    2. Alias: MaximumSalesPotential
    3. Base Table: Account
    4. Calculation Type: Numeric
    5. Description: The highest sales potential of open opportunities for the account
  2. Calculation tab
    1. In the top box, scroll to and expand the Opportunity(Accountid->Accountid) [inner] join
    2. Scroll to and click SALESPOTENTIAL.

      {ACCOUNT.OPPORTUNITY.SALESPOTENTIAL} is added to the calculation box.

    3. Click the Aggregate drop-down arrow and select Max.
  3. 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

Minimum Sales Potential per Account

  1. Properties tab – specify the following:
    1. Name: MinSalesPotential
    2. Alias: MinimumSalesPotential
    3. Base Table: Account
    4. Calculation Type: Numeric
    5. Description: The lowest sales potential of open opportunities for the account
  2. Calculation tab
    1. In the top box, scroll to and expand the Opportunity(Accountid->Accountid) [inner] join
    2. Scroll to and click SALESPOTENTIAL.

      {ACCOUNT.OPPORTUNITY.SALESPOTENTIAL} is added to the calculation box.

    3. Click the Aggregate drop-down arrow and select Min.
  3. 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

Sum of Actual Amount

  1. Properties tab – specify the following:
    1. Name: SumActualAmount
    2. Alias: SumActualAmount
    3. Base Table: Account
    4. Calculation Type: Numeric
    5. Description: The total actual amount of won opportunities for the account
  2. Calculation tab
    1. In the top box, scroll to and expand the Opportunity(Accountid->Accountid) [inner] join.
    2. Scroll to and click ACTUALAMOUNT

      {ACCOUNT.OPPORTUNITY.ACTUALAMOUNT} is added to the calculation box.

    3. Click the Aggregate drop-down arrow and select Sum.
  3. 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

Average Sales Potential of Open Opportunities

  1. Properties tab – specify the following:
    1. Name: AverageSalesPotential
    2. Alias: AvgSalesPotential
    3. Base Table: Account
    4. Calculation Type: Numeric
    5. Description: The average sales potential for open opportunities for the account
  2. Calculation tab
    1. In the top box, scroll to and expand the Opportunity(Accountid->Accountid) [left] join
    2. Scroll to and click SALESPOTENTIAL.

      {ACCOUNT.OPPORTUNITY.SALESPOTENTIAL} is added to the calculation box.

    3. Click the Aggregate drop-down arrow and select Average.
  3. 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