QPR ProcessAnalyzer Expression Query Examples

This page contains example KPI Analyses.

Example 1

The following analysis calculates:

1. Number of cases
2. List of case names
3. List of distinct case names (single case appears only once in the list)

for each event attribute Color and Category.

{
  "Root": "Events",
  "Dimensions": [
    {
      "Name": "Color",
      "Expression": "Color"
    },
    {
      "Name": "Category",
      "Expression": "Category"
    }
  ],
  "Values": [
    {
      "Name": "Count",
      "Expression": "Count(_)" 
    },
    {
      "Name": "Cases",
      "Expression": "StringJoin(\",\", _.Case.Name)" 
    },
    {
      "Name": "DistinctCases",
      "Expression": "StringJoin(\",\", Distinct(_.Case.Name))" 
    }
  ],
  "Ordering": [ 
    {
      "Name": "Color",
      "Direction": "Ascending"
    },
    {
      "Name": "Category",
      "Direction": "Descending"
    }
  ]
}

Example 2

Same analysis for for case attributes:

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "Color",
      "Expression": "Color"
    },
    {
      "Name": "Category",
      "Expression": "Category"
    }
  ],
  "Values": [
    {
      "Name": "Count",
      "Expression": "Count(_)" 
    },
    {
      "Name": "Cases",
      "Expression": "StringJoin(\",\", _.Name)" 
    }
    ],
  "Ordering": [ 
    {
      "Name": "Color",
      "Direction": "Ascending"
    },
    {
      "Name": "Category",
      "Direction": "Ascending"
    }
  ]
}

The following analysis returns a list of all dimensions but doesn't calculate any KPI's:

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "Region",
      "Expression": "Region"
    }
  ]
}

The following analysis calculates a KPI for all filtered data but doesn't slice it to any dimensions:

{
  "Root": "Cases",
  "Values": [
    {
      "Name": "Case count",
      "Expression": "Count(_)" 
    }
    ]
}

3 Grams

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "CaseId",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Expression": "
        Flatten(
          _
          .Array(
            StringJoin(\"->\", 
            Array(\"0\", 
              GetAt(0, Events).Type.Name, 
              If(
                Count(Events) > 1, 
                GetAt(1, Events).Type.Name, 
                \"0\"
                )
              )
            ),
            StringJoin(\"->\", 
              Events.Where(!IsNull(NextInCase))
              .Array(Type.Name, 
                NextInCase.Type.Name, 
                If(
                  !IsNull(NextInCase.NextInCase), 
                  NextInCase.NextInCase.Type.Name, 
                  \"0\"
                )
              )
            )
          )
        ).Where(_ != \"\")
      ",
      "DimensionOrderExpression": "OrderByValue(_)",
      "Type": "Pivot"
    }
  ],
  "Ordering": [ 
    {
      "Name": "CaseId",
      "Direction": "Ascending"
    }
  ]
}

Following analysis shows case duration distribution by hours (works like the Duration Analysis)

{
  "Root": "Cases",
    "Dimensions": [
      {
        "Name": "Case Duration in Hours",
        "Expression": "Duration.TotalHours.Round(0)"
      }
    ],
    "Values": [
      {
        "Name": "Case Count",
        "Expression": "Count(_)" 
      }
    ],
    "Ordering": [ 
      {
        "Name": "Case Duration in Hours",
        "Direction": "Ascending"
    }
  ]
}

The following analysis calculates monthly average case duration. Case start month needs to be a dimension and KPI is the average case duration (also rounding is used).

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "Start Month",
      "Expression": "StartTime.Month"
    }
  ],
  "Values": [
    {
      "Name": "Average Case Duration in Days",
      "Expression": "Round(Average(_.Duration.TotalSeconds) / 3600, 0)"
    }
  ],
  "Ordering": [
    {
      "Name": "Start Month",
      "Direction": "Ascending"
    }
  ]
}

Dynamic Exampes

The following analysis shows how many of different kinds of events occurred by time (works like the Event Type Trend Analysis).

{
  "Root": "EventTypes",
  "Dimensions": [
    {
      "Name": "Event Name",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Name": "Event Count",
      "Expression": "GetAt(0, _.Count)" 
    },
    {
      "NameExpression": "",
      "Name": "DateGroupDim",
      "ValueDimensionExpression": "TimeRange(DateTime(2012, 1, 1, 12), DateTime(2012, 1, 1, 13), TimeSpan(0, 0, 10))",
      "Expression": "GetAt(0, Count(_.Events.Where(TimeStamp.Round(TimeSpan(0, 0, 10)) == DateGroupDim)))",
      "Type": "Dynamic"
    }
  ],
  "Ordering": [ 
    {
      "Name": "Event Name",
      "Direction": "Ascending"
    }
  ]
}

Pivot Exampes

Event type ordering

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "CaseId",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Expression": "
        Flatten(
          _
          .Events
          .For(\"i\", IndexInCase + 1, i < Count(Case.Events), i + 1, 
            StringJoin(\">\", 
              Array(
                Type.Name, 
                GetAt(i, Case.Events).Type.Name
              )
            )
          )
        )
      ",
      "DimensionOrderExpression": "OrderByValue(_)",
      "Type": "Pivot"
    }
  ],
  "Ordering": [ 
    {
      "Name": "CaseId",
      "Direction": "Ascending"
    }
  ]
}

Repeats

{
  "Root": "Cases",
  "Dimensions": [
    {
      "Name": "CaseId",
      "Expression": "Name"
    }
  ],
  "Values": [
    {
      "Expression": "
        Flatten(
          _
          .FindRepeats(Events.Type.Name)
          .Repeat(
            Count(GetAt(1, _)) - 1, 
            StringJoin(\"->\", GetAt(0, _))
          )
        )
      ",
      "DimensionOrderExpression": "OrderByValue(_)",
      "Type": "Pivot"
    }
  ],
  "Ordering": [ 
    {
      "Name": "CaseId",
      "Direction": "Ascending"
    }
  ]
}