QPR ProcessAnalyzer Expression Query Examples

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"
    }
  ]
}

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"
		}
	]
}

Duration

{
	"Root": "Cases",
	"Dimensions": [
		{
			"Name": "TotalHours",
			"Expression": "Duration.TotalHours.Round(0)"
		}
	],
	"Values": [
		{
			"Name": "Count",
			"Expression": "Count(_)" 
		}
	],
	"Ordering": [ 
		{
			"Name": "TotalHours",
			"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"
    }
  ]
}

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"
		}
	]
}

Event type trend

{
	"Root": "EventTypes",
	"Dimensions": [
		{
			"Name": "Name",
			"Expression": "Name"
		}
	],
	"Values": [
		{
			"Name": "Visible Count",
			"Expression": "GetAt(0, _.Count)" 
		},
		{
			"NameExpression": "",
			"Name": "DateGroupDim",
			"ValueDimensionExpression": "TimeRange(DateTime(2012, 1, 1, 12), DateTime(2012, 1, 1, 13), TimeSpan(0, 0, 10))",
			"SelectionExpression": "_.Events.Where(TimeStamp.Round(TimeSpan(0, 0, 10)) == DateGroupDim)",
			"Expression": "GetAt(0, Count(Selection))",
			"Type": "Dynamic"
		}
	],
	"Ordering": [ 
		{
			"Name": "Name",
			"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"
		}
	]
}