QPR ProcessAnalyzer Expression Query Examples: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
Line 45: Line 45:
</pre>
</pre>


For case attributes:
Same analysis for for case attributes:
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "Cases",
"Dimensions": [
  "Dimensions": [
{
    {
"Name": "Color",
      "Name": "Color",
"Expression": "Color"
      "Expression": "Color"
},
    },
{
    {
"Name": "Category",
      "Name": "Category",
"Expression": "Category"
      "Expression": "Category"
}
    }
],
  ],
"Values": [
  "Values": [
{
    {
"Name": "Count",
      "Name": "Count",
"Expression": "Count(_)"  
      "Expression": "Count(_)"  
},
    },
{
    {
"Name": "Cases",
      "Name": "Cases",
"Expression": "StringJoin(\",\", _.Name)"  
      "Expression": "StringJoin(\",\", _.Name)"  
}
    }
],
    ],
"Ordering": [  
  "Ordering": [  
{
    {
"Name": "Color",
      "Name": "Color",
"Direction": "Ascending"
      "Direction": "Ascending"
},
    },
{
    {
"Name": "Category",
      "Name": "Category",
"Direction": "Ascending"
      "Direction": "Ascending"
}
    }
]
  ]
}
}
</pre>
</pre>


1 dimension selected:
The following analysis returns a list of all dimensions but doesn't calculate any KPI's:
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "Cases",
"Expressions": [
  "Dimensions": [
{
    {
"Expression": "Color",
      "Name": "Region",
"Values": ["Red"]
      "Expression": "Region"
}
    }
]
  ]
}
}
</pre>
</pre>


2 dimensions selected:
The following analysis calculates a KPI for all filtered data but doesn't slice it to any dimensions:
<pre>
<pre>
{
{
"Root": "Cases",
  "Root": "Cases",
"Expressions": [
  "Values": [
{
    {
"Expression": "Color",
      "Name": "Case count",
"Values": ["Red"]
      "Expression": "Count(_)"  
},
    }
{
    ]
"Expression": "Category",
"Values": [2]
}
]
}
}
</pre>
</pre>
Line 191: Line 187:
</pre>
</pre>


Monthly Average Case Duration
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).
<pre>
<pre>
{ "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" }]}
{
  "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"
    }
  ]
}
</pre>
</pre>



Revision as of 10:05, 28 November 2017

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