This article describes the different options for applying transformations to the fields in your dataset.
Quick Links
Introduction
By applying filters, you can optimize the information in your fields for your business analysis. Remove any unnecessary noise, highlight the most valuable aspects and summarize information in many useful ways.
There are several field settings and filtering options:
- Settings for date, numeric, and string fields, including label renaming, aggregation, sorting, formatting, and conditional formatting.
- Filters for empty values.
- Filters to select specific values.
- Filters by Rule.
Field settings and rules are accessible through the visualization editor, which is displayed automatically when a dataset is added to the dashboard canvas.
Field Settings
This section describes the different options for applying transformations to datasets, such as sorting, filtering, and aggregating data.
In order to apply formatting or filter to your data, you must select the specific field you want to modify in the corresponding placeholder (Category, Columns, Label, Rows and Values). There are three types of fields, each of which has their own formatting and filter options:
- Date Fields
- Numeric Fields
- String Fields
Note: All fields dropped in the Values placeholder of the data editor will be formatted as numbers.
Date Fields
- Label renaming: rename your fields for display purposes only. Fields are always referenced by their original name in calculated field expressions. Once renamed, erase the label completely to go back to the original value.
- Fiscal year initial month: if your 12-month period is not the same as a calendar year, you can use Fiscal Years and select the initial month for your period.
- Sorting: sort your dates in either ascending or descending order.
- Date Format: select from a predefined list of date and time formats to display your date/time values.
Date Fields Hierarchies
Date Fields aggregation is displayed as a hierarchical list in the Fields section. You can find Year, Quarter, Month, Day, and Minutes, depending on the level of granularity of your Date field.
When you drag and drop a Date field in the Pivot Editor, all the hierarchy is automatically displayed (see below).
In addition, dragging a specific hierarchy level will show data for that level only, as shown below:
Numeric Fields
- Label renaming: rename your fields for display purposes only. Fields are always referenced by their original name in calculated field expressions. Once renamed, erase the label completely to go back to the original value.
- Aggregation: configure how you want your numbers to be processed by Altitude (sum, count, average, etc.).
- Sorting: sort your numbers in either ascending or descending order.
- Is Visible: choose whether the data in this field will be used in the current visualization.
- Type: define whether your number should be formatted as percentage, currency, or a simple number. If you select Currency, you will have the option to choose the type of currency (dollar, euro, pounds, and yens).
- Fraction digits: select the number of decimal places you want to display.
- 1000 Separator: configure whether or not to display the thousands separator.
- Negative Numbers: select between enclosing negative numbers in parentheses or prefixing with the minus (-) sign.
- Large Numbers Formatting: display M for millions and K for thousands within the visualization preview in the central pane.
String Fields
- Label renaming: rename your fields for display purposes only. Fields are always referenced by their original name in calculated field expressions. Once renamed, erase the label completely to go back to the original value.
- Sorting: sort your field in either ascending or descending order.
Sort by Field
Altitude allows you to change how the data in a visualization is displayed by sorting the data in ascending or descending order. In addition, it is also possible to further control how a visualization looks with sorting by selected fields in your original data source.
The Sort by field option is part of the fields settings but is hidden by default. To enable it:
- Select a field under the Label placeholder.
- In Field Settings, choose Ascending or Descending for Sorting.
- Sort by a specific field will show under the Sorting setting. You can choose a field from the drop-down on the right (as shown above).
Ad-Hoc Hierarchies
While Altitude creates hierarchies for Year-Month-Date date fields automatically, this does not happen for other types of fields. Ad-hoc hierarchies allow users to analyze the data in charts, gauges, and grid views, allowing users to create new hierarchies with the fields located in the Rows placeholder of the data editor.
Ad-hoc hierarchies are not supported in Text, Circular Image, Grid and Text View charts.
Enabling Ad-Hoc Hierarchies
To enable ad-hoc hierarchies, just drag and drop a field in the Add Hierarchy section of the Rows placeholder in the data editor. When you do this, the structure and behaviour of the data table will change.
As seen in the screenshot above, a single column will replace multiple columns. In the example presented in the screenshot, you not only can drill up and down, but you can also expand each Manufacturer to show the Device Name.
Supported Visualizations
Ad-hoc hierarchies can be added to all visualizations except for the following ones:
- Circular Gauges
- Grid Charts
- Text Gauges
- Text Views
- Image Charts
Conditional Formatting
Conditional formatting allows you to provide different formatting of a cell (or rows for Text Views) depending on the values found in a numeric column. For instance, values in the lower 50% range of a grid can be colored with a red adorner to signal very low values.
The conditional formatting configuration allows you to establish styling rules per ranges of data up to three ranges (typically used for styling, upper, middle, and lower ranges). Depending on the nature of the information, low values might be a good or bad signal, so you have the flexibility to configure the styling in the way that makes the most sense in your situation.
Enabling Conditional Formatting
To enable conditional formatting on a numeric column, select field in the Data Editor to prompt the Field Settings dialog. The conditional formatting configuration is the last option in the settings and is disabled by default.
- Limits: these values are automatically set as the lowest and highest values in the dataset for the specified column, but can also be overridden manually with constant values.
- Data Ranges: the three ranges you will use to style your data. For all ranges, you can select one of the pre-defined indicators and colors in the drop-down.
- Value comparison type: whether you want the ranges to be percentages or numbers.
- When value is ≥: the formatting for values greater than the number you enter.
- When value is ≥ and <: this is a fixed range that depends on the values you enter in the first and third range.
- When value is <: the formatting for values less than the number you enter.
Supported Visualizations
Conditional formatting can be applied to the following visualizations:
- Grid Chart
- Pivot Chart
- Text View
Calculated Fields
Altitude allows you to define new fields in the data set, named calculated fields. These fields are created by using expressions (formulas). An expression can be a combination of existing field(s), constant values and:
- Predefined functions
- Simple math calculations and other functions that are not predefined
There are two types of calculated fields:
- Pre-calculated (also called "calculated")
- Post-calculated
Pre-Calculated Fields
Pre-calculated fields are evaluated before executing Data Editor aggregations. This means in order to apply a certain formula, Additionality will go through every record in your field's dataset once or several times. Because of this, pre-calculation is likely to be underperforming in terms of speed when working with large datasets.
To add a new pre-calculated field, you need to:
- Click/tap on the + button in the Fields panel.
- Choose Calculated Field.
3. The New Calculated Field screen will open up.
4. Here you will need to:
4.1 Assign a name to your new pre-calculated field.
4.2 Enter a Formula (expression). In the Fields section you will find a list of all the existing fields to choose from. You can use one or more fields to create the formula by clicking on the selected field or typing its name in square brackets. Choose one of the predefined functions listed in the Functions section or use a simple math calculation (as shown above).
5. The new pre-calculated field will show up in your Fields list. From here on, you can use it for your visualization.
In the example above, the new calculated field is used with a grid visualization where no aggregation is being applied on the data fields. Pre-calculated fields can also be used with Pivot Grids. In this case, aggregation (e.g. summarization) will be applied to the already calculated records in the pre-calculated field.
Post-Calculated Fields
Post-calculated fields can be created when working with Pivot tables and other visualizations, which execute an aggregation on the data fields in the Data Editor. Post-calculated fields are always built by applying a formula on already summarized values.
To create a post calculated field you will need to click the Fx button above the values section and then follow the same process as previously detailed.
If you need to use other fields from your data source, that are not included in the Data Editor, you can add them by clicking/tapping on the + button next to Values. Since post-calculated fields are created only by using aggregated values, you will first need to select an aggregation from the drop-down list to be executed on the data field.
Post-calculation tends to perform better than pre-calculation when working with large datasets.
Using the Predefined Analytics Functions
For both pre-calculated and post-calculated fields, you can use one of the available functions within Additionality:
- Aggregation: average, averageif, count, countif, max, maxif, min, minif.
- Date: date, datevalue, day, formatdate, fquarter, semester, fsemester, fyear, hour, millisecond, minute, month, monthname, monthshortname, applytimezone, currenttimezone, datetimefromunixts, now, quarter, second, time, today, weekday, weeknum, year.
- Information: empty, isempty.
- Logic: and, false, if, not, or, true.
- Lookup & Reference: previous, row.
- Math: abs, exp, log, log10, mod, rand, randbetween, sign, sqrt, trunc.
- String: concatenate, find, len, lower, mid, replace, sortinterval, trim, upper.
Limitations to IF Conditions. IF conditions have known limitations when included in aggregation functions in pre-calculated fields. The need to go through every record that many times, trying to check an IF condition within an aggregation formula causes underperformance issues, hence it's not supported.
Creating Calculated Fields without using the predefined functions
You can also create Calculated Fields without using any of the predefined functions; for instance, for simple math calculations like subtractions, divisions, additions or multiplications.
What to consider when using the Analytics functions
- Text strings should be included between quotation marks.
- Examples include locale ("en") and date formats ("dd/mm/yyyy").
- Fields included in your formula should be included between square brackets.
- Examples include [Device Name], [Is Managed] and [OS Version] for the v_DeviceInfo Dataset.
- Examples include [Device Name], [Is Managed] and [OS Version] for the v_DeviceInfo Dataset.
Calculated Fields Further Information
Aggregation Calculated Fields
Aggregation formulas are useful for you to work with your original data source in order to dissect its values, often re-organizing, or simply summarizing the information contained in it. You can also use them to calculate different values you are focused on (average, for example), find top and bottom values (max and min), etc. All formulas are, therefore, meant to be used with numerical fields only.
|
Function Name and Description |
Function Syntax |
|
average: The average aggregation will return a number, which will be calculated from the average value of all rows in your selected expression. |
average({expression}) |
|
averageif: Using a regular function with an if-condition means that the results you get need to meet certain criteria, which will be defined within your condition. |
averageif({expression},{if-condition}) |
|
count: The count aggregation will return a number, which is the number of rows in your data source. There are no additional arguments required. |
count() |
|
countif: Using a regular function with an if-condition means that the results you get need to meet certain criteria, which will be defined within your condition. |
countif({if-condition}) |
|
max: The max aggregation will return a number, which is the highest number in your selected expression. |
max({expression}) |
|
maxif: Using a regular function with an if-condition means that the results you get need to meet certain criteria, which will be defined within your condition. |
maxif({expression},{if-condition}) |
|
min: The min aggregation will return a number, which is the lowest number in your selected expression. |
min({expression}) |
|
minif: Using a regular function with an if-condition means that the results you get need to meet certain criteria, which will be defined within your condition. |
minif({expression},{if-condition}) |
|
sum: The sum aggregation will return a number, which is calculated as the sum of all rows in your selected expression. |
sum({expression}) |
|
sumif: Using a regular function with an if-condition means that the results you get need to meet certain criteria, which will be defined within your condition. |
sumif({expression},{if-condition}) |
Calculated Fields with IF Conditions
Using a regular function (which needs an expression) with an if-condition means that the results you get need to meet certain criteria, which will be defined within your condition.
Syntax
By default, you will see the following structure when you select any of the functions with the "IF" suffix.
XXXXXXIF({expression},{if-condition})
There are two arguments that you will need to configure:
- expression: choose one of the fields in your data source.
- if-condition: the if condition will require that you perform a logical test. The if-condition requires a logical test, which is the condition your expression needs to meet for the aggregation to be calculated.
Date Calculated Fields
Date formulas can be used to output different date and time information in your widget.
Note: Your calculated field will show the information you include in the formula, overwriting of the formatting you have set for Date and Time.
The functions included in the date category are:
|
Function Name |
Syntax and Sample |
|
date: date will return a date set to the values you include in your formula. |
Syntax: date({year},{month},{day},{hour},{minute},{second}) |
|
datevalue: datevalue converts applies a specified format to a date with no initial format (date). It then returns a date in a speicified language (locale). |
Syntax: datevalue({date},{format},{locale}) |
|
day: day uses the date syntax, and returns the third value in the formula. |
Syntax: day({date}) |
|
formatdate: formatdate uses the date syntax, and applies a specified format to the date. It then returns a text with a date in a specified language (locale). |
Syntax: formatdate({date},{format},{locale}) |
|
fquarter: fquarter uses the date syntax and a second argument, number, and returns the quarter of the fiscal year for the specified date. |
Syntax: fquarter({date},{number}) |
|
fyear: fyear uses the date syntax and a second argument, number, and returns the fiscal year for the specified date. |
Syntax: fyear({date},{number}) |
|
hour: hour uses the date syntax, and returns the fourth value in the formula. |
Syntax: hour({date}) |
|
millisecond: millisecond uses the time syntax and returns the fourth value in the formula. |
Syntax: millisecond({time}) |
|
minute: minute uses the date syntax, and returns the fifth value in the formula. |
Syntax: minute({date}) |
|
month: month uses the date syntax, and returns the second value in the formula. |
Syntax: month({date}) |
|
monthname: monthname uses the date syntax, and returns the month name for a date. |
Syntax: monthname({date},{locale}) |
|
monthshortname: monthshortname uses the date syntax, and returns the month name in a short, three-letter, format for a date. |
Syntax: monthshortname({date},{locale}) |
|
now: now returns the current date and time. |
Syntax: now() |
|
quarter: quarter uses the date syntax, and returns the quarter to which your date belongs. |
Syntax: quarter({date}) |
|
second: second uses the date syntax, and returns the sixth value in the formula. |
Syntax: second({date}) |
|
time: When you use time, Analytics will return a time set to the values you include in your formula. |
Syntax: time({hour},{minute},{second},{millisecond}) |
|
today: today returns the current date. |
Syntax: today() |
|
weekday: weekday uses the date syntax, and returns the day of the week your date falls into. |
Syntax: weekday({date}) |
|
weeknum: weeknum uses the date syntax, and returns the number of the week your date falls into within the specified year. |
Syntax: weeknum({date}) |
|
year: year uses the date syntax, and returns the first value in the formula. |
Syntax: year({date}) |
|
semester: Semester uses the datetime syntax and returns the semester of the date(a numer from 1 to 2). |
Syntax: semester({datetime}) |
|
fsemester: fsemester uses the date syntax and a second argument, number, and returns the semester of the fiscal year for the given date. |
Syntax: fsemester({date}, {number}) |
|
applytimezone: applytimezone uses the date syntax and a second argument - timezone - and returns a date set to the year, month, day, hour, minute and second of the date parameter for the given time zone |
Syntax: applytimezone({date}, {timezone}) |
|
currenttimezone: returns the time zone of the requesting user |
Syntax: currenttimezone() |
|
datetimefromunixts: datetimefromunixts uses the miliseconds syntax and returns a date time value for the given Unix timestamp. |
Syntax: datetimefromunixts ({miliseconds}) |
Information Calculated Fields
Information calculated fields are meant to be used to test the value of a selected field. They are particularly useful with large spreadsheets because you can check what type of information you are working with before performing a second calculation.
The functions included in the information category are:
|
Function Name |
Syntax |
|
empty: empty can be used to insert a column with empty cells. There are no arguments to configure. |
empty() |
|
isempty: isempty evaluates the specified expression, and checks if any row in your data source has empty values. |
isempty({value}) |
Logic Calculated Fields
Logic calculated fields can be used to compare two or more values in your data source. They always return "0" or "1", depending on the logical test you submit your values to.
In Additionality, logic calculated fields include:
- Functions with no arguments: true() and false(), which return 1 and 0 respectively.
- Complex functions with logical tests.
The functions included in the logic category are:
|
Function Name |
Syntax and Sample |
|
and: and runs two logical tests. If the logical test is true, it returns 1. If one or both are false, it returns 0. |
Syntax: and({logical1},{logical2}) |
|
false: false returns 0, the logical value of false. |
Syntax: false() |
|
if: if runs a logical test. If the logical test is true, it returns 1. If the logical test is false, it returns 0. |
Syntax: if({logical test},{value if true},{value if false}) |
|
not: not runs a logical test. If the logical test is false, it returns 1. If the logical test is true, it returns 0. |
Syntax: not({logical}) |
|
or: or runs two logical tests (if statements). If either one of the logical tests is true, it returns 1. If both are false, it returns 0. |
Syntax: or({logical1},{logical2}) |
|
true: true returns 1, the logical value of true. |
Syntax: true() |
String Calculated Fields
String calculated fields, except for sortinterval, allow you to edit texts to get different results.
Remember to always include strings between quotation marks (" ").
The functions included in the String category are:
|
Function Name |
Syntax and Sample |
|
concatenate: concatenate allows you to join multiple strings of text to form a phrase. Spaces are not automatically included, so make sure you include them in your text arguments if necessary. |
Syntax: concatenate() |
|
find: find returns the starting position (number) of a first string of text in a second string if text as specified in your arguments. |
Syntax: find({find text},{within text},{start number}) |
|
len: len returns the number of characters in the string of text you enter. |
Syntax: len({text}) |
|
lower: lower converts all upper case characters in a given text string to lower case. |
Syntax: lower({text}) |
|
mid: mid returns a substring (length) of the specified string of text according to what you configure in your arguments. |
Syntax: mid({text},{start},{length}) |
|
replace: replace replaces a given string of text with a different text as specified in your arguments. |
Syntax: replace({text},{old text},{new text}) |
|
sortinterval: sortinterval returns a value in a(n) interval(s) according to what is configured in the arguments. The string is returned with format NN [from,to] |
Syntax: sortinterval() |
|
trim: trim returns the same string of text you enter; however, it will remove any leading or trailing whitespaces, and will only keep the spaces between words. |
Syntax: trim({text}) |
|
upper: upper converts all lower case characters in a given text string to upper case. |
Syntax: upper({text}) |