Group by in Automated Indicator

[HasarinFareeth]

Hi - I have created an automated indicator that pulls the records from SLA Breakdown by Assignment group table to measure the Incident SLAs for my supplier. This table has records for all the assignment groups but the breached column is set based on the SLA definition for the supplier at each SLA. I could see there are multiple records been pulled for same incident with same SLA which is the facts that the the incident can be re-assigned to other groups within the same supplier. I would like to group these records by Task (Incident Number) and Task SLA (SLA Definition) so that the SLA at the supplier level can be calculated as below using the formula indicator. 

% of No of SLAs Met by the Supplier / No of SLAs Triggered for the Supplier. 

Thanks

Fareeth

[G Ponsekar]

Hi @HasarinFareeth ,

Can you try like below and check

1. Create a new Indicator Source for "Incident SLAs for Supplier".
Facts Table: SLA Breakdown by Assignment Group.
Conditions:
Filter the records to include only those relevant to your supplier (e.g., Assignment Group.Supplier = [Your Supplier Name]).
Filter by the specific SLA Definitions you want to track for the supplier.
Consider the timeframe for the SLA calculation (e.g., Start date within the last month).

2. Create two automated indicators:
"No. of SLAs Met by the Supplier":
Source: Use the Indicator Source you just created.
Aggregate: Count distinct on Task (Incident Number) and Task SLA (SLA Definition).
Conditions: Add a filter Breached = false.
"No. of SLAs Triggered for the Supplier":
Source: Use the same Indicator Source.
Aggregate: Count distinct on Task (Incident Number) and Task SLA (SLA Definition).
Conditions: No additional breach conditions, as this counts all triggered SLAs for the supplier, regardless of breach status.

3. Create a new Formula Indicator for "Supplier SLA Performance".
Unit: Percent.
Formula: [[No. of SLAs Met by the Supplier]] / [[No. of SLAs Triggered for the Supplier]] * 100.
Save the indicator and publish.

If I could help you with your Query then, please hit the Thumb Icon and mark as Correct !!

Thanks, GP

[HasarinFareeth]

Thanks a lot. This really helps me but I am not able to aggregate the count distinct with 2 fields (i.e., Task and Task SLA) as it allows me to select only one field as in the below screen shot. 

Thanks 

Fareeth

[G Ponsekar]

Hi @HasarinFareeth ,

Yeah COUNT DISTINCT is typically designed to operate on a single column to find unique values within that column

Can you try concat both fields and perform COUNT DISTINCT

 create a new field by concatenating the values of Task and Task SLA with a separator (e.g., CONCAT(Task, '-', 'Task SLA')). Then, perform a COUNT DISTINCT on this newly created concatenated field. This effectively creates a unique identifier for each distinct combination of Task and Task SLA.

If I could help you with your Query then, please hit the Thumb Icon and mark as Correct !!

Thanks, GP

[HasarinFareeth]

Thanks for the response. 

I was trying to do this through scripting instead of a new field. 

(function() {

    var distinct = {};

    var gr = new GlideRecord("sla_breakdown_by_assignment");

    gr.query();

    while (gr.next()) {

            var key = gr.getValue("task") + "|" + gr.getValue("task_sla") + "|" + gr.getValue("breached");

            distinct[key] = true;

    }

    return Object.keys(distinct).length;

)();

but this is not working as expected. I am trying to concatenating 3 fields for grouping as mentioned above. 

Thanks

Fareeth