When working with a query you can apply filters, create calculated columns, or apply aggregation operations using the data table tools. These operations help you turn your data into real, usable information and can be described as beloging to three categories:
- Aggregation operations - These operations perform mathmatical calculations on sets of grouped values. To use these, your table must already be grouped by time period. The aggregation operation is then applied to the sets of values in each time period grouping.
- Filtering operations - These operations filter the data in your table to either include or exclude rows that meet the filter condition you specify.
- Create column operations - These operations can apply calculations or use lookup tables to generate new columns based on data that already exists in your table.
Operations can also be directly writen in the query script using LINQ language. To learn more about this, see the Performing operations using LINQ article.
Operations over columns
The Operations Over Columns window contains all you need to set up any kind of operation. There are two different ways to open this window.
- Select Aggregation, Filter, or Create Column from the query toolbar.
- Select Additional Tools, then select Aggregation, Filter, or Create Column.
The Operations Over Columns window contains tabs where you can configure and apply Aggregation, Filter, and Create Column operations.
The Create Column and Aggregate Function tabs contain the same fields. Both types of operations create a new column to contain the results of the selected operation performed on the selected argument(s). For example, the capture below shows an aggregation that will add a new column called HTTPrequests that will contain the count of grouped values in the method column.
The Filter Data tab contains different fields and options because a filter does not add a column, rather the reults of the selected operation performed on the selected argument(s) will be the inclusion or exclusion of rows from the query data. For example, the capture below shows a filter that will exclude (negated) records that contain a value in the countMethod column that is less than or equal to 150.
In each tab, you need to select an Operation from the drop-down list, then click New Argument to activate the field where you identify the necessary arguments. These two fields are interdependent. That is to say, the system will automatically validate or reject certain arguments based on the operation you have slected. Similarly, the system will identify valid operations in green and invalid operation in orange based on any arguments you have selected. For example, the capture below shows that for the selected argument eventdate, the operations that can be performed on that type of field are in green, while the invalid operations are shown in orange.
Each operation requires a specific number or type of argument(s). For more information about an operation's requirements, click the info icon next to the Operation field as shown below.
- The name, group and description of the operation selected.
- The format of the operation. This operation has only one format, but some others accept different combinations, which are always indicated here (e.g. the Rounding operation has two different formats: round(arg_1) → result and round(arg_1, arg_2) → result). You can also check the number of arguments needed (in this case 2) and the required format of the data you link to each argument (string, integer, float...).
- Some specific examples of the operation.
- In case you have too many or not enough arguments, the system will automatically send you a warning message.
- In case you have not chosen the proper arguments, the system will automatically notify you.