Formatting Text, Numbers, Times and Processedges

Text

Since data used for analyses is mostly represented in numbers, dates or process paths, formatting text is not a very common task in SAP Process Mining by Celonis and will mostly be necessary when using OLAP Tables. The standard editing menu for components already offers options for font color, size and weight that have been explained in the respective chapters. In case these options are not sufficient, some more functions for editing text exist. These will be used in the formula field of dimensions and aggregations and on columns that contain string values.

UPPER(text)

Converts the string specified in the ‘text’ parameter to uppercase. The text parameter can for example be the name of a column containing string values.

TRIM(text)

This function removes spaces (if there are any) in front of the string specified as ‘text’ parameter. The text parameter can for example be the name of a column containing string values.

Numbers

Numbers play a very big role when analyzing data and thus should be presented in an easily understandable manner. For that purpose, SAP Process Mining by Celonis offers some formatting functions for numbers.

TEXT(value; text)

Although the name does not seem to represent a function for numbers, the TEXT function can be used on numbers to turn them into texts according to a given format. Thus, the TEXT function is a very useful tool for making numbers more easily readable. Using the TEXT function, specifications can be made about whether or not a thousands separator will be used or how many decimal places should be displayed in the data. Please note that no specifications can be made regarding the characters used for thousands and decimal separators since these will be configured in the “My Profile” settings described in The User Menu.

The value parameter specifies the value the format will be applied to. This will in most cases be the $value variable that can be used as a representation for the values inside a column. The text parameter then specifies the format and must be given in single quotation marks.

Example: To convert the value 123456 into a number with thousands separator and two decimal places, we use the TEXT function as follows: TEXT(12345, ‘0,000.00’) and get 12,345.00 as result.

FLOOR(value)

The FLOOR function will round the given number down to the nearest integer value. No matter the value of the decimal place, the number will always be rounded down. The value parameter specifies the value that will be rounded down. This will in most cases be the $value variable that can be used as a representation for the values inside a column.

Example: To round down 3.9 to 3, we use the FLOOR function as follows: FLOOR(3.9).

CEILING(value)

The CEILING function will round the given number up to the nearest integer value. No matter the value of the decimal place, the number will always be rounded up. The value parameter specifies the value that will be rounded up. This will in most cases be the $value variable that can be used as a representation for the values inside a column.

Example: To round up 3.1 to 4, we use the CEILING function as follows: CEILING(3.1).

ROUND(value; digits)

The ROUND function will round numbers up or down to a certain number of decimal places. If the digit that will disappear due to rounding is below 5, the value will be rounded down to the closest integer. If it is equal to five or greater, the value will be rounded up to the closest integer.

The value parameter specifies the value that will be rounded up or down. This will in most cases be the $value variable that can be used as a representation for the values inside a column. The digits parameter specifies the number of decimal places the value will be rounded to. However, the digits parameter can also take negative values. If a negative parameter is used, the system will behave as if the decimal separator was moved to the left, then round the value (without deleting the decimal places) and then move the decimal separator to the right again.

Example: To round 123.56 to one decimal place resulting in 123.6, we use ROUND follows: ROUND(123.56, 1). However, ROUND(123.56, -2) will result in 100.

Times

FLEXITIME(value)

Flexitime automatically assures the correct and intuitive presentation of time values. Value contains the duration and depending on its length the process explorer shows year, month, week, day, hour, minute or second for the throughput time.

Processedges

GRADIENT(value, from, to, min, max)

The Gradient function helps to set the width of the process edges depending on the number of cases passing through.

For that, the function needs the information if it should calculate with absolute or ralative figures ($value oder $relative). As an advice you should use the relative figures, because for the absolute ones you need to know in which spectrum the number of cases, that pass a process edge, are. (from,to)

Additionaly you need to give the maximum and minimum for the width of the process edges.

Example (relative):

=GRADIENT($relative;0;100;1.5;3.5)

Example (absolute):

=GRADIENT($value;0;30000;1.5;4.5)

COLORGRADIENT(value, from, to, minColor, maxColor)

The Colorgradient can be used to vary the color of the process edges, depending on the number of cases that pass through.

For that, the function needs the information if it should calculate with absolute or ralative figures ($value oder $relative). As an advice you should use the relative figures, because for the absolute ones you need to know in which spectrum the number of cases, that pass a process edge, are. (from,to)

Additionaly you can choose two colors. The color for the process edges will be chosen out of the spectrum between those two colors.