Measure, Dimension and Column Settings: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
 
(48 intermediate revisions by the same user not shown)
Line 1: Line 1:
[[File:Chartsettingsmeasuresettings.png|right|650px]]
Measure/dimension/column settings are for the settings that are specific to individual measure/dimension/column. When hovering the measure/dimension/column in the chart settings, an icon to open the measure/dimension/column appears. This page describes all the measure/dimension/column settings. Note that only the applicable settings are shown depending e.g. the visualization type and data type of the measure/dimension/column.


When hovering a measure/dimension/column settings, the first of the appearing icons opens a dialog for the measure/dimension/column settings. The following settings are available for measures/dimensions/columns:
== General settings ==
== General tab ==
[[File:Chartsettingsmeasuresettings.png|right|700px]]
* '''Custom label''': By default, measures/dimensions/columns have automatically created descriptive labels shown for example as chart axis titles or table column names (depending on the visualization). Use the custom label to define your own label which replaces the automatic label. If you want to hide the label, you can use a space as the custom title.
* '''Custom label''': By default, measures/dimensions/columns have automatically created descriptive labels shown for example as chart axis titles or table column names (depending on the visualization). Use the custom label to define your own label which replaces the automatic label. If you want to hide the label, you can use a space as the custom title.
* '''Color''': In charts, color for visualizing this measure/dimension/column, appearing for example as a column or line color.
* '''Color''': In charts, color for visualizing this measure/dimension/column, appearing for example as a column or line color.
* '''Visualization''': In charts, series specific visualization for this measures/dimensions/columns. Overrides the default visualization defined in the ''General'' tab.
* '''Visualization''': In charts, series specific visualization for this measures/dimensions/columns. Overrides the default visualization defined in the ''General'' tab.
* '''Mapping in chart''': When using the chart, defines how this measure/dimension/column is mapped in the chart, for example to Y-axis, X-axis, series etc.
* '''Mapping to visualization''': Defines how this measure/dimension/column is mapped in the visualization, for example to Y-axis, X-axis, series etc. Available mappings depend on the visualization type. For some mappings, multiple of the mappings can be selected at the same time, and for some only one of the mappings can be selected at a time. For example, a measure/dimension/column can only have one of the following mappings: Y-axis, X-axis, and series. Also, for some mappings only one measure/dimension/column can have the mapping at a time, so when selecting that kind of mapping, it's automatically unselected from the measure/dimension/column where it was previously selected. For example, X-axis and series can be mapped only to one measure/dimension/column at a time.
* '''Round to decimals''': Number of decimals to show for decimal numbers. Integers (whole numbers) are always shown without decimals, so they don't have this field. Note that despite the rounding, values are sorted based on the accurate values.
* '''Pivot table aggregation''': When pivot table is used, defines the aggregation method used for the pivot values.
* '''Unit''': Unit that is shown with each measure/dimension/column value to indicate the unit of the value (e.g. days, euros, cases etc.). In many measures/dimensions/columns, units are determined automatically, but user can always override the unit if desired. It's also possible to use a unit from other measure/dimension/column which can be achieved using following syntax in the Unit field: e.g. ${measure:1}, ${dimension:2}, ${column:3} (similar than in the table web links).
* '''Unit position''': Determines how units are shown with the value. Units can be shown before or after the value, and there can be a space between or not. Unit can also be hidden.
* '''Unit position''': Determines how units are shown with the value. Units can be shown before or after the value, and there can be a space between or not. Unit can also be hidden.
* '''Date format''': Dates are presented using this date format. For example: dd.MM.yyyy HH:mm:ss, M/d/yyyy HH:mm.
* '''Date format''': Dates are presented using this date format. Note that this field is only visible, when the type of the data is date. For example: dd.MM.yyyy HH:mm:ss, M/d/yyyy HH:mm.
* '''Date interval''': For dimensions, date values are divided into dimension values using this interval setting.
* '''Date interval''': When dimensioning data, date values are divided into dimension value slots using this interval setting. Note that this field is only available for dimensions. For Snowflake chart, this setting is in the chart setting dialog.
* '''Hidden''': For tables, measures/dimensions/columns can be hidden using this checkbox. For example, conditional formatting can use data from the hidden columns.
* '''Hidden''': For tables, measures/dimensions/columns can be hidden using this checkbox. For example, conditional formatting can use data from the hidden columns.


== Table actions tab ==
== Special values ==
* '''Web link''': When defined (works in tables only), the measure/dimension/column will be a clickable web link opening the target web site to a new tab. Only the text in the table cell is clickable, so filtering is possible when clicking next to the text. To refer to the data in the same row, web links can contain tags (e.g. '''${dimension:1}'''), which are replaced with the data. You can also refer to the clicked value with '''${this}'''. If the web link only consists of a single tag, the tag value is not encoded (because the tag is assumed to contain a full url). If the web link contains static text in addition to the tags, the tag values are encoded using encodeUriComponent (because tags are assumed to be used as url parameters). If the link does not start with http:// or https://, the link is interpreted as a relative link, and it's added to the end of the QPR ProcessAnalyzer url. Examples: ''<nowiki>https://google.com/search/q=${measure:2}</nowiki>'', ''<nowiki>https://devnet.onqpr.com/pawiki/index.php?search=${this}</nowiki>''
It's possible to configure that certain special values (null, empty string, true, false) are shown in certain ways. Defining textual representations for these special values is a better practice than customizing the expression to show certain texts. Special values are:
* '''Variables to set on click''': Variables that are set when clicking the text in the column. Similar tags as for the web link, can be used both for the variable name and variable value fields. If there are either the sys:dashboard or sys:dashboardIdentifier variable among the changed variables (i.e. clicking will go to other dashboard), the variables are set to the session level; otherwise they are set to the dashboard level. If defining the Filter or Comparison variable with an empty value, the corresponding filter or comparison rule is created automatically (works only for dimensions).    
* '''Show for null value''': When an expression gives a null value, the text defined in this field is shown. By default empty text.
* '''Show for empty string''': When an expression gives an empty string value, the text defined in this field is shown. By default ''(empty)''.
* '''Show for boolean true''': When an expression gives a boolean value ''true'', the text defined in this field is shown. By default ''Yes''.
* '''Show for boolean false''': When an expression gives a boolean value ''false'', the text defined in this field is shown. By default ''No''.


== Special values tab ==
== Advanced settings ==
* '''Show for null value''': When an expression provides a null value, the text defined in this field is shown in the visualization. By default empty text.
* '''Adjustment expression''': Expression that is added to the end of the measure/dimension/column expression. The adjustment expression can be used for example to convert or map values. This setting is not available for the Snowflake chart.
* '''Show for empty string''': When an expression provides an empty string value, the text defined in this field is shown in the visualization. By default ''(empty)''.
* '''Calculate measure for''': Measure can be calculated for ''each dimension separately'' (the default option) which is the normal dimensioning behavior. It's also possible to ignore the dimensioning for this measure and calculate the measure for the ''currently filtered data'' (i.e. the measure is calculated for the entire dataset that the chart is based on). Furthermore, it's also possible to ignore filters and calculate the measure value for the ''entire model data''. This setting is only available for measures. This setting is not available for the Snowflake chart.
* '''Show for boolean true''': When an expression provides a boolean type of value ''true'', the text defined in this field is shown in the visualization. By default ''Yes''.
* '''Variable name''': Defines name for the variable containing this measure value. The variable can then be used in all measures that are below this measure. Variables can be used to define more complex measures (defined using custom measure expression) that consist of other measures (that are defined as variables). In the Snowflake chart, variables can be used with syntax ''Column("variableName")'', and in in-memory chart with syntax ''variableName''. This setting is not available for dimensions, and for columns this setting is available only in the in-memory chart. Spaces cannot be used in variable names.
* '''Show for boolean false''': When an expression provides a boolean type of value ''false'', the text defined in this field is shown in the visualization. By default ''No''.
* '''Statistical calculations''': Different kind of useful calculations that can be to measure/dimension/columns. Multiple statistical calculations can be applied at the same time. Some calculations require an additional parameter, that is specified in the ''Reference value for statistical calculations'' field. Available statistical calculations are listed in [[#Statistical_calculation_methods|Statistical calculation methods]].
* '''Reference value for statistical calculations''': This field is used to specify the reference numerical value needed by some of the statistical calculations. Note that there is only one field available, so only one statistical calculation needing a reference value can be used.
* '''Conditional formatting''': Defines dynamical background color, text color, icon or databar, to improve data visualization. Conditional formatting can be defined for [[QPR_ProcessAnalyzer_Table#Conditional_formatting|Table]], [[QPR_ProcessAnalyzer_Pivot_Table#Conditional_Formatting|Pivot Table]], and [[QPR_ProcessAnalyzer_KPI_Card#Conditional_Formatting|KPI Card]].
* '''Custom aggregation expression''': Define a custom aggregation expression to be used instead of the default aggregation expression. The aggregation expression calculates an aggregate value for the excluded rows that exceed the maximum value when ''Group Rows Exceeding Maximum'' is in use. This setting is not available for the Snowflake chart.


== Advanced tab ==
== Statistical calculations ==
* '''Adjustment expression''': Expression that is added to the end of the measure/dimension/column expression. The adjustment expression can be used for example to convert or map values.
Statistical calculations contain various useful conversions and adjustments that may be needed in measures, dimensions and columns. Each statistical calculation is applied to a specific measure, dimension or column, so make sure to choose the right one where to apply the statistical calculation. Several statistical calculations can be selected for the same measure, dimension or column, and they are applied in the order they are defined in the list. In addition, dimension statistical calculations are applied before the measure statistical calculations. Certain statistical calculations can only be applied to specific column data types, so only the applicable statistical calculations are shown. If the second dimension is defined, the statistical calculations are performed for each unique dimension value separately. Certain combinations of statistical calculations are not possible, so when selecting statistical calculations, some other statistical calculations may automatically be unselected.
* '''Calculate measure for''': Measure can be calculated for ''each dimension separately'' (the default option) which is the normal dimensioning behavior. It's also possible to ignore the dimensioning for this measure and calculate the measure for the ''currently filtered data'' (i.e. the measure is calculated for the entire dataset that the chart is based on). Furthermore, it's also possible to ignore filters and calculate the measure value for the ''entire model data''. This setting is only available for measures.
 
* '''Variable name''': Name of the variable containing this measure or column value. The variable can be used in expressions for other measures. The variable is created when a name for it has been defined. This setting is not available for dimensions.
The following statistical calculation options are available:
* '''Statistical calculations''': Different kind of calculations done to measure/dimension/column data. Several items from the list can be used at the same time. Some calculations require an additional parameter, that is specified in the ''Reference value for statistical calculations'' field. Available calculations are:
*'''Remove nulls''': Removes rows where the measure/dimension/column has a null value.
** '''Remove nulls''': Removes rows where the measure/dimension/column has a null value.
*'''Remove empty strings''': Removes rows where the measure/dimension/column has an empty string value. Applicable only for strings.
** '''Remove empty strings''': Removes rows where the measure/dimension/column has an empty string value.
*'''Remove outliers using normal distribution''': Removes rows where the measure/dimension/column has a value that is outside the [average] +/- X * [standard deviation]. The X is defined as the additional reference value. Applicable only for numbers.
** '''Remove outliers using normal distribution''': Removes rows where the measure/dimension/column has a value that is outside the [average] +/- X * [standard deviation]. The X is defined as an additional parameter.
*'''Remove insignificant values from start and end''': Removes rows from the beginning and end of the dataset the measure/dimension/column value is below the defined percentage of the maximum value in the dataset. This setting can be used to remove e.g. indiscernible items in a column chart. Applicable only for numbers.
** '''Remove insignificant values from start and end''': Removes rows from the beginning and end of the dataset the measure/dimension/column value is below the defined percentage of the maximum value in the dataset. This setting can be used to remove e.g. indiscernible items in a column chart.
*'''Remove numeric values lower than''': Removes rows where the measure/dimension/column value is lower than the defined limit. Requires numerical column. Applicable only for numbers.
** '''Remove values lower than''': Removes rows where the measure/dimension/column value is lower than the defined limit. Requires numerical column.
*'''Remove numeric values greater than''': Removes rows where the measure/dimension/column value is greater than the defined limit. Requires numerical column. Applicable only for numbers.
** '''Remove values greater than''': Removes rows where the measure/dimension/column value is greater than the defined limit. Requires numerical column.
*'''Remove dates older than (days)''': Removes rows where the measure/dimension/column value is earlier than the defined number of days. Applicable only for dates.
** '''Remove dates older than (days)''': Removes rows where the measure/dimension/column value is earlier than the defined number of days. Requires a date type of column.
*'''Remove dates older than (months)''': Removes rows where the measure/dimension/column value is earlier than the defined number of months. Applicable only for dates.
** '''Remove dates older than (months)''': Removes rows where the measure/dimension/column value is earlier than the defined number of months. Requires a date type of column.
*'''Remove dates that are in future''': Removes rows where the measure/dimension/column value is in future when comparing to the current time. Applicable only for dates.
** '''Remove dates that are in future''': Removes rows where the measure/dimension/column value is in future when comparing to the current time.
*'''Remove repeating values''': Removes rows where the measure/dimension/column value occurs the second or more time. i.e. only the first occurrence is preserved. Note that the order of data is relevant.
** '''Remove repeating values''': Removes rows where the measure/dimension/column value occurs the second or more time. i.e. only the first occurrence is preserved. Note that the order of data is relevant.
*'''Remove values repeating more than''': Removes rows where the measure/dimension/column value occurs more than the defined times. Note that the order of data is relevant.
** '''Remove values repeating more than''': Removes rows where the measure/dimension/column value occurs more than the defined times. Note that the order of data is relevant.
*'''Fill gaps in dimensioning''': Adds rows to the data to fill the "missing" values in the continuous sequence of numbers or dates. This is very useful in dimensions, because by default no dimension value is shown if there are no analyzed objects (e.g., cases or events) having the dimension value. When the data is numerical, all the missing integer values between the minimum and maximum values are created. When the data are dates, all period start timestamps are created between the minimum and maximum date. Applicable for numbers, dates and booleans.
** '''Fill gaps in dimensioning''': Rows rows to the data to fill "missing" values. When the data is numerical, all the missing integer values between the minimum and maximum values are created. When the data is dates, all months start timestamps are created between the minimum and maximum.
* '''Replace nulls with zeros''': Replaces null values with zeroes. Applicable only for numbers.
** '''Show cumulative values''': Calculates sum of values for the measure/dimension/column from the beginning until that data point.
* '''Replace zeros with nulls''': Replaces zero values with null values. Applicable only for numbers.
** '''Show change from previous''': Calculates difference to the previous value. The first value shows zero.
*'''Show cumulative values''': Calculates sum of values for the measure/dimension/column from the beginning until that data point. Applicable only for numbers.
** '''Show percentual change from previous''': Calculates a percentual change to the previous value. The shown unit changes to percentage. The first value shows zero.
*'''Show change from previous''': Calculates difference from the previous value. The first value shows zero. Applicable only for numbers.
** '''Smooth using moving average''': Calculates all measure/dimension/column values as an average of the nearest values. The additional parameter is the number of steps to go back and forth to take into account.
*'''Show percentage change from previous''': Calculates percentage change comparing to the previous value. The shown unit is percentages. The first value will show zero. Applicable only for numbers.
** '''Calculate moving sum''': Calculates sum for each measure/dimension/column values. Goes back number of steps defined by the additional parameter.
*'''Smooth using moving average''': Calculates measure/dimension/column values as average of the previous values. The reference value defines how many values are averaged. Applicable only for numbers.
** '''Add by''': Adds the specified number to all the measure/dimension/column values.
*'''Calculate moving sum''': Calculates a moving sum of the specified number of values (defined using reference value). Applicable only for numbers.
** '''Multiply by''': Multiplies all measure/dimension/column values by the specified number.
*'''Year-to-date average''': Calculates average of values between the start of the year until the current row. The time period is determined based on the first dimension. Applicable only for numbers.
** '''Divide by''': Divides all measure/dimension/column values by the specified number.
*'''Year-to-date sum''': Calculates sum of values between the start of the year until the current row. The time period is determined based on the first dimension. Applicable only for numbers.
** '''Sort by this column''': Sorts the data ascending by this measure/dimension/column.
*'''Add by''': Adds the specified number to the measure/dimension/column values. Applicable only for numbers.
** (not available in UI) '''replaceNullValues''', '''replaceEmptyStrings''', '''replaceTrueValues''', '''replaceFalseValues''': Replaces the respective data value (null, empty string (string), true value (boolean), false value(boolean) ) with any other value defined using the ''referenceValue'' parameter.
*'''Multiply by''': Multiplies measure/dimension/column values by the specified number. Applicable only for numbers.
* '''Reference value for statistical calculations''': The reference numerical value that is used in some of the statistical calculations.
*'''Divide by''': Divides measure/dimension/column values by the specified number. Applicable only for numbers.
* '''[[QPR_ProcessAnalyzer_Table#Conditional_formatting|Table conditional formatting]]''': Background color or text color based on data.
*'''Divide by other column:''' Divides values by another value in the same row in the other column specified by the column index (starting from 0). Dimensions can only refer to dimensions and measures to measures. Applicable only for numbers.
* '''Custom aggregation expression''': Define a custom aggregation expression to be used instead of the default aggregation expression. The aggregation expression calculates an aggregate value for the excluded rows that exceed the maximum value when ''Group Rows Exceeding Maximum'' is in use.
*'''Sort ascending by this column''': Sorts the data in an ascending order by this measure/dimension/column.
*'''Sort descending by this column''': Sorts the data in a descending order by this measure/dimension/column.
*'''Percentage of dimension object count''': Shows how many percentage the measure/dimension/column value is from the number of objects (i.e., rows) in the dimension. When analyzing cases, the objects are cases, and so on. Applicable only for numbers.
*'''Percentage of total case count''': Shows how many percentage the measure/dimension/column value is from the total number of cases in the filtered eventlog. Applicable only for numbers.
*'''Percentage of total event count''': Shows how many percentage the measure/dimension/column value is from the total number of events in the filtered eventlog. Applicable only for numbers.
*'''Percentage of total event type count''': Shows how many percentage the measure/dimension/column value is from the total number of event types in the filtered eventlog. Applicable only for numbers.
*'''Percentage of total variation count''': Shows how many percentage the measure/dimension/column value is from the total number of variations in the filtered eventlog. Applicable only for numbers.
*'''Percentage of total flow count''': Shows how many percentage the measure/dimension/column value is from the total number of flows in the filtered eventlog. Applicable only for numbers.
*'''Percentage of column sum''': Calculates how many percentage this row's value is of the sum of all values in this column. If the sum is zero, all values will be null. Applicable only for numbers.
*'''Difference from column average''': Calculates the absolute difference of this row's value to the average of all values in this column. Applicable only for numbers.
*'''Add row''': Add a new row in the end of the data containing the defined reference value. All other cells in the new contain null values.
*'''Row number''': Replaces the measure/dimension/column value with a row number in the dataset (starting from 1). As the row number is numerical, this statistical calculation is applicable only for numeric columns.
*'''Rank''': Replaces the measure/dimension/column value with a rank number. The rank is similar to the row number, except equal measure/dimension/column values get the same rank number. Dataset needs to be sorted by this measure/dimension/column to be used for the rank. As the rank is numerical, this statistical calculation is applicable only for numeric columns. More information about the rank: https://www.sqlshack.com/overview-of-sql-rank-functions/.
*'''Dense rank''': Replaces the measure/dimension/column value with a dense rank number. The dense rank is similar to the rank, except no numbers are skipped in case there are equal values. Dataset needs to be sorted by this measure/dimension/column to be used for the dense rank. As the dense rank is numerical, this statistical calculation is applicable only for numeric columns. More information about the dense rank: https://www.sqlshack.com/overview-of-sql-rank-functions/.
*'''NTile''': Divides the dataset into the given number of approximately equal groups and assigns a distinct number for each group (starting from 1). As the ntile is numerical, this statistical calculation is applicable only for numeric columns. More information about the ntile: https://www.sqlshack.com/overview-of-sql-rank-functions/.
*'''Nonconformance reason formatting''': Shows the json formatted nonconformance reasons (provided by the Snowflake ConformanceViolationsFlows function) as user-readable text. When a reference value of zero is used, the nonconformance reason is formatted as html where the event type names are bolded. When a reference value of one is used, the conconformance reasons are shown as pure text.


== Other settings ==
== Other settings ==
Also following settings are available (there is no UI, so these settings need to be defined for ''Chart Settings''):
Following settings are available without the UI, so the settings need to be defined directly in the ''Chart Settings'':
* '''Color mappings (colorMappings)''': Maps measure/dimension/column values to colors, so that selected values are always visualized with selected colors. Other values will get colors based on the chart's color palette. Color mappings defined to a measure/dimension/column that is mapped to X-axis or to series, takes effects in charts. There is no UI for this setting, so it needs to be defined directly to the dimension definition in the chart settings. Example:  
* '''colorMappings''': This setting can be used to ensure that certain data points are visualized with certain colors. For example, data point representing "yes" could be visualized with green color and data point with value "no" could be visualized with red. The colorMappings property needs to be defined for the measure/dimension/column which values determine the colors. Other values will get colors based on the chart color palette. For boolean values use "true" and "false" and for the null value use "null". Example:  
<pre>
<pre>
"colorMappings": {
"colorMappings": {
Line 65: Line 85:
}
}
</pre>
</pre>
* '''Show in datalabels (showInDatalabels)''': Defines whether the measure/dimension/column is shown in a chart data label (true/false). If any measure/dimension/column has showInDatalabels set to true, the default data labels are overridden by this setting to show only the defined ones. There is no UI for this setting, so it needs to be defined directly to the measure/dimension/column definition in the chart settings.
<pre>
* '''Top items (topItems)''': Shows only the defined number of dimension values which have the largest volumes (e.g. number of cases or events depending on what's analyzed). Data with the remaining values are now shown. If using only one dimension, the ''Maximum rows'' settings provides the same result, but the Maximum rows usually doesn't work in a desired way when there are multiple dimensions - the top items setting is for that purpose. The top items settings is not available for measures. There is no UI for this setting, so it needs to be defined directly to the dimension/column definition in the chart settings.
"colorMappings": {
  "true": "#68DD8F",
  "false": "#ef5254",
  "null": "#b9bdc1"
}
</pre>
* '''topItems''': Shows only the defined number of dimension values with the greatest volumes (e.g. number of cases or events depending on what's analyzed). If there is only one dimension, the ''Maximum rows'' settings gives the same result as the topItems setting. If there are several dimensions, the topItems setting is needed, as the ''Maximum rows'' setting works globally for the entire result data. Note that the topItems settings is not available for measures. There is no UI for this setting, so it needs to be defined directly to the dimension/column definition in the ''Chart Settings''.

Latest revision as of 17:42, 13 June 2024

Measure/dimension/column settings are for the settings that are specific to individual measure/dimension/column. When hovering the measure/dimension/column in the chart settings, an icon to open the measure/dimension/column appears. This page describes all the measure/dimension/column settings. Note that only the applicable settings are shown depending e.g. the visualization type and data type of the measure/dimension/column.

General settings

Chartsettingsmeasuresettings.png
  • Custom label: By default, measures/dimensions/columns have automatically created descriptive labels shown for example as chart axis titles or table column names (depending on the visualization). Use the custom label to define your own label which replaces the automatic label. If you want to hide the label, you can use a space as the custom title.
  • Color: In charts, color for visualizing this measure/dimension/column, appearing for example as a column or line color.
  • Visualization: In charts, series specific visualization for this measures/dimensions/columns. Overrides the default visualization defined in the General tab.
  • Mapping to visualization: Defines how this measure/dimension/column is mapped in the visualization, for example to Y-axis, X-axis, series etc. Available mappings depend on the visualization type. For some mappings, multiple of the mappings can be selected at the same time, and for some only one of the mappings can be selected at a time. For example, a measure/dimension/column can only have one of the following mappings: Y-axis, X-axis, and series. Also, for some mappings only one measure/dimension/column can have the mapping at a time, so when selecting that kind of mapping, it's automatically unselected from the measure/dimension/column where it was previously selected. For example, X-axis and series can be mapped only to one measure/dimension/column at a time.
  • Pivot table aggregation: When pivot table is used, defines the aggregation method used for the pivot values.
  • Unit position: Determines how units are shown with the value. Units can be shown before or after the value, and there can be a space between or not. Unit can also be hidden.
  • Date format: Dates are presented using this date format. Note that this field is only visible, when the type of the data is date. For example: dd.MM.yyyy HH:mm:ss, M/d/yyyy HH:mm.
  • Date interval: When dimensioning data, date values are divided into dimension value slots using this interval setting. Note that this field is only available for dimensions. For Snowflake chart, this setting is in the chart setting dialog.
  • Hidden: For tables, measures/dimensions/columns can be hidden using this checkbox. For example, conditional formatting can use data from the hidden columns.

Special values

It's possible to configure that certain special values (null, empty string, true, false) are shown in certain ways. Defining textual representations for these special values is a better practice than customizing the expression to show certain texts. Special values are:

  • Show for null value: When an expression gives a null value, the text defined in this field is shown. By default empty text.
  • Show for empty string: When an expression gives an empty string value, the text defined in this field is shown. By default (empty).
  • Show for boolean true: When an expression gives a boolean value true, the text defined in this field is shown. By default Yes.
  • Show for boolean false: When an expression gives a boolean value false, the text defined in this field is shown. By default No.

Advanced settings

  • Adjustment expression: Expression that is added to the end of the measure/dimension/column expression. The adjustment expression can be used for example to convert or map values. This setting is not available for the Snowflake chart.
  • Calculate measure for: Measure can be calculated for each dimension separately (the default option) which is the normal dimensioning behavior. It's also possible to ignore the dimensioning for this measure and calculate the measure for the currently filtered data (i.e. the measure is calculated for the entire dataset that the chart is based on). Furthermore, it's also possible to ignore filters and calculate the measure value for the entire model data. This setting is only available for measures. This setting is not available for the Snowflake chart.
  • Variable name: Defines name for the variable containing this measure value. The variable can then be used in all measures that are below this measure. Variables can be used to define more complex measures (defined using custom measure expression) that consist of other measures (that are defined as variables). In the Snowflake chart, variables can be used with syntax Column("variableName"), and in in-memory chart with syntax variableName. This setting is not available for dimensions, and for columns this setting is available only in the in-memory chart. Spaces cannot be used in variable names.
  • Statistical calculations: Different kind of useful calculations that can be to measure/dimension/columns. Multiple statistical calculations can be applied at the same time. Some calculations require an additional parameter, that is specified in the Reference value for statistical calculations field. Available statistical calculations are listed in Statistical calculation methods.
  • Reference value for statistical calculations: This field is used to specify the reference numerical value needed by some of the statistical calculations. Note that there is only one field available, so only one statistical calculation needing a reference value can be used.
  • Conditional formatting: Defines dynamical background color, text color, icon or databar, to improve data visualization. Conditional formatting can be defined for Table, Pivot Table, and KPI Card.
  • Custom aggregation expression: Define a custom aggregation expression to be used instead of the default aggregation expression. The aggregation expression calculates an aggregate value for the excluded rows that exceed the maximum value when Group Rows Exceeding Maximum is in use. This setting is not available for the Snowflake chart.

Statistical calculations

Statistical calculations contain various useful conversions and adjustments that may be needed in measures, dimensions and columns. Each statistical calculation is applied to a specific measure, dimension or column, so make sure to choose the right one where to apply the statistical calculation. Several statistical calculations can be selected for the same measure, dimension or column, and they are applied in the order they are defined in the list. In addition, dimension statistical calculations are applied before the measure statistical calculations. Certain statistical calculations can only be applied to specific column data types, so only the applicable statistical calculations are shown. If the second dimension is defined, the statistical calculations are performed for each unique dimension value separately. Certain combinations of statistical calculations are not possible, so when selecting statistical calculations, some other statistical calculations may automatically be unselected.

The following statistical calculation options are available:

  • Remove nulls: Removes rows where the measure/dimension/column has a null value.
  • Remove empty strings: Removes rows where the measure/dimension/column has an empty string value. Applicable only for strings.
  • Remove outliers using normal distribution: Removes rows where the measure/dimension/column has a value that is outside the [average] +/- X * [standard deviation]. The X is defined as the additional reference value. Applicable only for numbers.
  • Remove insignificant values from start and end: Removes rows from the beginning and end of the dataset the measure/dimension/column value is below the defined percentage of the maximum value in the dataset. This setting can be used to remove e.g. indiscernible items in a column chart. Applicable only for numbers.
  • Remove numeric values lower than: Removes rows where the measure/dimension/column value is lower than the defined limit. Requires numerical column. Applicable only for numbers.
  • Remove numeric values greater than: Removes rows where the measure/dimension/column value is greater than the defined limit. Requires numerical column. Applicable only for numbers.
  • Remove dates older than (days): Removes rows where the measure/dimension/column value is earlier than the defined number of days. Applicable only for dates.
  • Remove dates older than (months): Removes rows where the measure/dimension/column value is earlier than the defined number of months. Applicable only for dates.
  • Remove dates that are in future: Removes rows where the measure/dimension/column value is in future when comparing to the current time. Applicable only for dates.
  • Remove repeating values: Removes rows where the measure/dimension/column value occurs the second or more time. i.e. only the first occurrence is preserved. Note that the order of data is relevant.
  • Remove values repeating more than: Removes rows where the measure/dimension/column value occurs more than the defined times. Note that the order of data is relevant.
  • Fill gaps in dimensioning: Adds rows to the data to fill the "missing" values in the continuous sequence of numbers or dates. This is very useful in dimensions, because by default no dimension value is shown if there are no analyzed objects (e.g., cases or events) having the dimension value. When the data is numerical, all the missing integer values between the minimum and maximum values are created. When the data are dates, all period start timestamps are created between the minimum and maximum date. Applicable for numbers, dates and booleans.
  • Replace nulls with zeros: Replaces null values with zeroes. Applicable only for numbers.
  • Replace zeros with nulls: Replaces zero values with null values. Applicable only for numbers.
  • Show cumulative values: Calculates sum of values for the measure/dimension/column from the beginning until that data point. Applicable only for numbers.
  • Show change from previous: Calculates difference from the previous value. The first value shows zero. Applicable only for numbers.
  • Show percentage change from previous: Calculates percentage change comparing to the previous value. The shown unit is percentages. The first value will show zero. Applicable only for numbers.
  • Smooth using moving average: Calculates measure/dimension/column values as average of the previous values. The reference value defines how many values are averaged. Applicable only for numbers.
  • Calculate moving sum: Calculates a moving sum of the specified number of values (defined using reference value). Applicable only for numbers.
  • Year-to-date average: Calculates average of values between the start of the year until the current row. The time period is determined based on the first dimension. Applicable only for numbers.
  • Year-to-date sum: Calculates sum of values between the start of the year until the current row. The time period is determined based on the first dimension. Applicable only for numbers.
  • Add by: Adds the specified number to the measure/dimension/column values. Applicable only for numbers.
  • Multiply by: Multiplies measure/dimension/column values by the specified number. Applicable only for numbers.
  • Divide by: Divides measure/dimension/column values by the specified number. Applicable only for numbers.
  • Divide by other column: Divides values by another value in the same row in the other column specified by the column index (starting from 0). Dimensions can only refer to dimensions and measures to measures. Applicable only for numbers.
  • Sort ascending by this column: Sorts the data in an ascending order by this measure/dimension/column.
  • Sort descending by this column: Sorts the data in a descending order by this measure/dimension/column.
  • Percentage of dimension object count: Shows how many percentage the measure/dimension/column value is from the number of objects (i.e., rows) in the dimension. When analyzing cases, the objects are cases, and so on. Applicable only for numbers.
  • Percentage of total case count: Shows how many percentage the measure/dimension/column value is from the total number of cases in the filtered eventlog. Applicable only for numbers.
  • Percentage of total event count: Shows how many percentage the measure/dimension/column value is from the total number of events in the filtered eventlog. Applicable only for numbers.
  • Percentage of total event type count: Shows how many percentage the measure/dimension/column value is from the total number of event types in the filtered eventlog. Applicable only for numbers.
  • Percentage of total variation count: Shows how many percentage the measure/dimension/column value is from the total number of variations in the filtered eventlog. Applicable only for numbers.
  • Percentage of total flow count: Shows how many percentage the measure/dimension/column value is from the total number of flows in the filtered eventlog. Applicable only for numbers.
  • Percentage of column sum: Calculates how many percentage this row's value is of the sum of all values in this column. If the sum is zero, all values will be null. Applicable only for numbers.
  • Difference from column average: Calculates the absolute difference of this row's value to the average of all values in this column. Applicable only for numbers.
  • Add row: Add a new row in the end of the data containing the defined reference value. All other cells in the new contain null values.
  • Row number: Replaces the measure/dimension/column value with a row number in the dataset (starting from 1). As the row number is numerical, this statistical calculation is applicable only for numeric columns.
  • Rank: Replaces the measure/dimension/column value with a rank number. The rank is similar to the row number, except equal measure/dimension/column values get the same rank number. Dataset needs to be sorted by this measure/dimension/column to be used for the rank. As the rank is numerical, this statistical calculation is applicable only for numeric columns. More information about the rank: https://www.sqlshack.com/overview-of-sql-rank-functions/.
  • Dense rank: Replaces the measure/dimension/column value with a dense rank number. The dense rank is similar to the rank, except no numbers are skipped in case there are equal values. Dataset needs to be sorted by this measure/dimension/column to be used for the dense rank. As the dense rank is numerical, this statistical calculation is applicable only for numeric columns. More information about the dense rank: https://www.sqlshack.com/overview-of-sql-rank-functions/.
  • NTile: Divides the dataset into the given number of approximately equal groups and assigns a distinct number for each group (starting from 1). As the ntile is numerical, this statistical calculation is applicable only for numeric columns. More information about the ntile: https://www.sqlshack.com/overview-of-sql-rank-functions/.
  • Nonconformance reason formatting: Shows the json formatted nonconformance reasons (provided by the Snowflake ConformanceViolationsFlows function) as user-readable text. When a reference value of zero is used, the nonconformance reason is formatted as html where the event type names are bolded. When a reference value of one is used, the conconformance reasons are shown as pure text.

Other settings

Following settings are available without the UI, so the settings need to be defined directly in the Chart Settings:

  • colorMappings: This setting can be used to ensure that certain data points are visualized with certain colors. For example, data point representing "yes" could be visualized with green color and data point with value "no" could be visualized with red. The colorMappings property needs to be defined for the measure/dimension/column which values determine the colors. Other values will get colors based on the chart color palette. For boolean values use "true" and "false" and for the null value use "null". Example:
"colorMappings": {
  "Robert Miller": "#68DD8F",
  "Patricia White": "#ef5254",
  "Mary Wilson": "#b9bdc1"
}

"colorMappings": {
  "true": "#68DD8F",
  "false": "#ef5254",
  "null": "#b9bdc1"
}
  • topItems: Shows only the defined number of dimension values with the greatest volumes (e.g. number of cases or events depending on what's analyzed). If there is only one dimension, the Maximum rows settings gives the same result as the topItems setting. If there are several dimensions, the topItems setting is needed, as the Maximum rows setting works globally for the entire result data. Note that the topItems settings is not available for measures. There is no UI for this setting, so it needs to be defined directly to the dimension/column definition in the Chart Settings.
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=Measure,_Dimension_and_Column_Settings&oldid=24824"