Jump to: navigation, search

Formula

Overview

Starting with the 8.5.106 release, Stat Server supports statistical category Formula.

Formulas (defined in SQL-like language) allow you to calculate statistics that could not be calculated using hard-coded categories.
With formulas you can:

Historical custom formula aggregates (COUNT, SUM, AVG, MIN, MAX, and so on) are only updated in the following two situations:

  • Upon reset
  • Upon action or status end (depending on the Subject parameter of the statistic)

Historical aggregates in hard-coded categories are also updated while status is in progress.

Important
  • In addition to the Growing aggregation interval, supported in the 8.5.106 release, starting with the 8.5.107 release, Stat Server supports Sliding, Selection, and SinceLogin aggregation intervals.
  • Sliding and Selection aggregation intervals are applicable to the DNAction subject only.
  • Formulas serialization into/from the backup file is supported.
  • The logical NOT (~) character does not work with aliases for the group of actions, such as CallWait on mediation DNs. To exclude the whole group, each member should be excluded explicitly. For example: *,~CallWaitUnknown,~CallWaitConsult,~CallWaitInbound,~CallWaitOutbound

Examples

The formula itself has to be specified in the Expression option:

Category=Formula
Objects=<objects>
Expression=<formula expression>
Subject=<subject>
<business attribute name>=<business attribute value>

Stat Type Description
Category = Formula

Subject = DNAction
Objects = Queue
Expression = @COUNT(CallWait; duration > 10)

A statistic with this Stat Type, requested for a Queue, returns the current number of calls on this Queue with duration more than 10 seconds.
Category = Formula

Subject = DNAction
Objects = Agent
Expression = AVG(CallInbound; kvnum(udata, "Priority"))

A statistic with this Stat Type, requested for an Agent, returns an average value of the "Priority" User Data key of received by this Agent inbound calls (CallInbound actions generated).

Options for Formula Stat Type

Option Applicability Comments
Category Mandatory Has to be set to Formula.
Objects Mandatory See the Stat Type Configuration Options table for the description of the Objects option.
Subject Mandatory See the Stat Type Configuration Options table for the description of this option.
Expression Mandatory The formula has to be specified in this option. Stat Server Actions from the Expression are used for the Selection time profile if MainMask and RelMask are absent.
MainMask Special Applicable only for the Selection time profile if the RelMask is absent and ignored in all other cases. See the Stat Type Configuration Options table for the description of this option.
RelMask Special Applicable only for the Selection time profile and ignored in all other cases. See the Stat Type Configuration Options table for the description of this option.
<business attribute> Optional See the Stat Type Configuration Options table for the description of this option.
UseSourceTimeStamps Optional See the Stat Type Configuration Options table for the description of this option.
ApplyFilterAtActionEndOnly Optional See the Stat Type Configuration Options table for the description of this option.
Description Optional See the Stat Type Configuration Options table for more information.
DynamicOverloadPolicy Optional Defines actions that Stat Server may apply to a given statistic to reduce the overload.
JavaSubCategory N/A Not applicable.
ReasonStartOverridesStatusStart N/A Not applicable.
Formula N/A Not applicable.
Important
  • TimeRanges, if needed, should be included as conditions in the Expression.
  • Filters, TimeProfile, Object name are included in a New API Statistic request.

Aggregates

Using formulas, users can create customized versions of the existing hard-coded categories, combine different statistical aggregates, and so on. Aggregates are the basis of any formula and provide the way of calculating values:

Aggregates
Aggregator Parameters Description Example
@COUNT 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. condition Optional. Boolean expression, depending on action/status attributes, interval attributes and system variables
Current aggregator that returns current number of actions or statuses listed in action_types_list that matches the condition if provided. @COUNT(CallInternal, CallInbound, CallConsult; kvexists(udata, "Language", "English")) – returns current number of actions/statuses listed, that have "Language", "English" key-value pair in attached UserData.
@SUM 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. expression Mandatory. Numeric expression, depending on action/status attributes, interval attributes and system variables
3. condition Optional. Boolean expression, depending on action/status/st attributes, interval attributes and system variables
Current aggregator that returns current sum of numeric expressions, computed for each action or status, satisfying the condition. @SUM(CallWait; kvnum(global_udata, "amount", -1)) – returns current sum of the last value in each CallWait action of "amount" key in attached UserData.
@AVG 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. expression Mandatory. Numeric expression, depending on action/status attributes, interval attributes and system variables
3. condition Optional. Boolean expression, depending on action/status attributes, interval attributes and system variables
Current aggregator that returns current average of numeric expressions, computed for each action or status, satisfying the condition. @AVG(CallOutbound; duration; duration > 0 & duration < 5) – returns current average duration of CallOutbound actions/statuses with duration less than 5 seconds.
@MIN 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. expression Mandatory. Numeric expression, depending on action/status attributes, interval attributes and system variables
3. condition Optional. Boolean expression, depending on action/status attributes, interval attributes and system variables
Current aggregator that returns current minimum of numeric expressions, computed for each action or status, satisfying the condition. @MIN(WaitForNextCall; duration) – returns current minimum duration of WaitForNextCall action/status
@MAX 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. expression Mandatory. Numeric expression, depending on action/status attributes, interval attributes and system variables
3. condition Optional. Boolean expression, depending on action/status attributes, interval attributes and system variables
Current aggregator that returns current maximum of numeric expressions, computed for each action or status, satisfying the condition. @MAX(NotReadyForNextCall; duration; kvexists(reasons, "ReasonCode", "Break")) – returns current maximum duration of NotReadyForNextCall action/status with "ReasonCode" = "Break"
COUNT 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. condition Optional. Boolean expression, depending on action/status attributes, interval attributes and system variables
Historical aggregator that returns total number of actions or statuses listed in action_types_list that matches the condition if provided. COUNT(CallInternal, CallInbound, CallConsult; kvexists(udata, "Language", "English")) – returns total number of actions/statuses listed, that have "Language" = "English" key-value pair in attached UserData.
SUM 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. expression Mandatory. Numeric expression, depending on action/status attributes, interval attributes and system variables
3. condition Optional. Boolean expression, depending on action/status attributes, interval attributes and system variables
Historical aggregator that returns sum of numeric expressions, computed for each action or status, satisfying the condition. SUM (CallInternal, CallInbound, CallConsult; duration; kvnum(udata, "amount", -1) < 100) – returns sum of actions/statuses duration if this action/status has last value of "amount" key in attached UserData less than 100.
AVG 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. expression Mandatory. Numeric expression, depending on action/status attributes, interval attributes and system variables
3. condition Optional. Boolean expression, depending on action/status attributes, interval attributes and system variables
Historical aggregator that returns average of numeric expressions, computed for each action or status, satisfying the condition. AVG(CallWait; duration; duration >= 10) – returns average duration of CallWait action if action duration was more than or equal to 10 seconds.
MIN 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. expression Mandatory. Numeric expression, depending on action/status attributes, interval attributes and system variables
3. condition Optional. Boolean expression, depending on action/status attributes, interval attributes and system variables
Historical aggregator that returns minimum of numeric expressions, computed for each action or status, satisfying the condition. MIN(AgentActive; duration) – returns minimum duration of AgentActive action.
MAX 1. action_types_list Mandatory. Comma separated list of Stat Server actions or statuses
2. expression Mandatory. Numeric expression, depending on action/status attributes, interval attributes and system variables
3. condition Optional. Boolean expression, depending on action/status attributes, interval attributes and system variables
Historical aggregator that returns maximum of numeric expressions, computed for each action or status, satisfying the condition. MAX(CallInbound; kvmax(udata, "amount"); kvexists(udata, "Language", "English")) – returns maximum value of "amount" key in UserData of CallInbound actions/statuses for which key-value pair "Language" = "English" also be presented in UserData.

Action Attributes

The following attributes are used in expression and condition parameters of aggregators:

Attribute Description
Attributes
duration A duration of Stat Server action/status.
start A timestamp of the start time of Stat Server action/status.
end A timestamp of the end time of Stat Server action/status.
udata UserData key-value list. List of data that is used in key-value pair functions as the first argument.
global_udata Global UserData key-value list. List of data that is used in key-value pair functions as the first argument.
reasons Reasons key-value list. List of data that is used in key-value pair functions as the first argument.
extensions Extensions key-value list. List of data that is used in key-value pair functions as the first argument.
system System key-value list. List of data that is used in key-value pair functions as the first argument.

System Variable Attributes

The aggregation interval parameter can be used in current Formulas (those, containing only current aggregates) with the interval != Growing. Current Formulas do not aggregate across the Selection interval.

The following parameter is available for current statistics:

System Variables
Attribute Description
$istart An aggregation interval start. For example, when interval=Sliding the @SUM( CallInternal; end-greatest(start,$istart) ) adjusts the duration of all current actions to the start of the window.

Mathematical Functions

The table below lists mathematical functions that are used in the expression and condition parameters of aggregators as well as in independent clauses:

Mathematical Functions
Function Description
abs Absolute value
ceil Ceiling of the value
floor Floor of the value
log Logarithm of the value
exp Exponent of the value
sqrt Square root of the value
sin Sinus of the value
cos Cosinus of the value
pow Power of the value
 % Module (for example, 5%2=1, and 64%4=0)


All mathematical functions have one float argument, except for the pow and % functions, which have two float arguments.

String Functions

String functions are applicable to set parameters, for example, "Language" and values returned by the kvstr function: kvstr(udata, "Language"). The table below lists string functions:

String Functions
Function Description
sub Substring
upper Conversion to upper case
lower Conversion of lower case
concatenation (operator +) Concatenation of strings
convert String conversion to a numeric value. For example, convert("32.58") returns the 32.58 numeric value.


All string functions have a single argument of the type string, except for the concatenation (+) function, which combines two strings.

Key-Value List Operations

The key-value list functions are similar to those presented in the UserData Properties. The following collections are taken as the first parameter:

  • udata
  • global_udata
  • reasons
  • extensions
  • system

The functions below can be applied to collections:

Key-Value List Functions
Function Description Similar Operator in UserData Properties
kvstr A string extraction from the key-value list. GetString
kvlist An embedded key-value list extraction from the key-value list. GetList
kvnum A number extraction from the key-value list. GetNumber
kvsum A Sum of numbers in the key-value list. GetSum
kvmin A minimum value for a given key in the key-value list. GetMin
kvmax A maximum value for a given key in the key-value list. GetMax
kvavg An average value for a given key in the key-value list. GetAver
kvexists True if a given key or pair <key,value> exists in the key-value list. False otherwise. PairExists

Rank Computations

Ranking functions (see the table below) are used in conditions of aggregators to rank Stat Server actions by some parameters. There are two arguments for rank functions: a function of actions attributes, and a positive integer (rank).

Ranking Functions
Function Description
rank_lt True, if the rank of an action, according to a condition, is less than the predefined number.
rank_gt True, if the rank of an action, according to a condition, is greater than the predefined number.


Example

If you want to calculate the (current) average duration of the 5 longest waiting calls in queue use the following expression:

@AVG( CallWait; duration; rank_lt( duration, 6 ) )

Sorting

Sorting functions:

  • greatest - the greatest value in a list.
    greatest( x1, x2, …, xN ), where x1, …, xN are numbers.
  • least - the least value in a list.
    least( x1, x2, …, xN ), where x1, …, xN are numbers.

can take any numeric values (including aggregators) as arguments. Unlike other functions, sorting functions have variable number of comma separated arguments.

Example

Find the largest number of the current Inbound, Outbound or Consult calls:

greatest( @COUNT(CallInbound), @COUNT(CallOutbound), @COUNT(CallConsult) )

Branching

The branching construction (<conditions> ? <expression if condition is true> : <expression if condition is false>) can be used in any part of the formula.

Branching <boolean expression> ? < num_expr1> : <num_expr2> means that if the <boolean expression> is true, then return the <num_expr1>, else return the <num_expr2>”.

Operators and their Priorities

Operators and their priority (see the table below) can be used in any part of the formula expression.

Operator Priority Associativity (which operator occurrence is applied first when the same priority operators appear in a row)
^ 10 Left
- (unary minus) 9 None
~ 8 Right
* 7 Left
/ 7 Left
 % 7 Left
+ 6 Left
- 6 Left
< 5 Left
> 5 Left
<= 5 Left
>= 5 Left
= 5 Left
 != 5 Left
& 4 Left
| 3 Left
 : 2 Right
 ? 1 Right

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on December 21, 2017, at 12:35.