Creating Calculations in Zoomdata
Calculations are custom fields that are created to perform analysis on one or more fields in your data source. Calculations can provide additional metrics and results to help you analyze and gather insights into your dataset. Zoomdata provides the capability for you to create calculations using your connected data sources.
Zoomdata's approach to custom calculations is based on the following fundamentals:
- Lets business users create complex formulas without IT support or technical knowledge
- Utilizes all (numeric) types of fields from the data source
- Works with the raw data (that is, the Field IDs)
- Filters results to provide focus to specific criteria
- Edits to the formulas can be easily made and saved
The calculations functionality is available to all Zoomdata-supported data sources (see Figure 1). Keep in mind that a data source needs to be connected to Zoomdata before the calculations functionality is accessible. After you have created a custom calculation, it is available as a metric source in your charts and dashboards, allowing you to explore and interact with them as any other attributes or metrics.
Zoomdata Calculation functionality uses a diverse array of building blocks including literal expressions, functions, and operators. As a result, you can create complex calculations that includes aggregate, table and window functions as well as filters. Basic arithmetic operators are used to build your formulas and literal expressions can be used to filter the results to specific criteria. For example, calculations of percentages, totals and averages can be made in the datasets. A Calculations Editor tool is used to help simplify the process of creating formulas and testing them to ensure validity (as shown in Figure 2).
Calculations are crafted by defining a formula that is based on a set of predefined functions and operators available in Zoomdata in conjunction with the Field IDs from the data source(s). You can also use custom calculated fields created in one formula as a source in a different formula.
After creating custom calculations, these metrics will be accessible in the chart axes labels. Users will then be able to select these custom calculations like any other attribute that is available to them (as shown in Figure 3).
The following topics are addressed in this article. You can jump directly to any of them:
Note that this article only provides overview information about using calculations as a filter. More detailed information and instructions are available in the article Applying Filtered Metrics .
Zoomdata supports the following types of calculations:
Column aggregations (specifically, SUM , AVG , MIN , MAX , COUNT , and COUNTD ) using basic arithmetic functions (including addition, subtraction, division and multiplication)
Table functions (such as TableSUM to calculate a percent of total)
Window functions (such as WindowAVG to calculate a metric based on the selected attribute)
In addition, filters can be applied in calculations, including:
Attribute options (for example, Sum of Sales where State = Florida)
Date field options (including common time formats such as YTD , MMDDYYYY , and YoY )
Numeric fields and (references to) existing calculations (including >, <, =, !=)
Date functions (including DateSUB and DateADD to derive a date in the formula)
Column Aggregation (SUM, AVG, MIN, MAX, COUNT, COUNTD)
Aggregations can be used to perform basic mathematical calculations on columns of data. Basic arithmetic formulas including addition, subtraction, multiplication and division of numeric measures in datasets are supported. Arithmetic formulas help you manipulate datasets in order to derive unique metrics that otherwise are not available. You can also create percentage metrics using the basic arithmetic functions.
Arithmetic capabilities include:
The ability to add, subtract, multiply and divide aggregated values across the dataset in a Group By;
Arithmetic operators used in calculations: addition (+), subtraction (-), multiplication (*) and division (/)
The flexibility to use either Field IDs from the dataset or a constant numeric value (such as 100 to calculate a percentage)
Table 1 shows the aggregated functions that is supported by the Zoomdata Calculations functionality:
|SUM (FieldID*)||Numeric||Returns the sum of all the values within the group**. This function is used with numeric fields only.||SUM(Sales)|
|AVG (FieldID)||Numeric||Returns the average of all the values within the group. This function is used with numeric fields only.||AVG(Margin)|
|MIN (FieldID)||Numeric||Returns the minimal value across a group.||MIN(Profit)|
|MAX (FieldID)||Numeric||Returns the maximal value across a group.||MAX(Price)|
|COUNT (FieldID)||Any||Returns the number of values within the group. When the 'FieldID' is specified, this function ignores fields containing 'null'. If using the wildcard character (*) for the 'FieldID', this function returns the number of records for the entire table, including 'null' fields.||
|COUNTD (FieldID)||Any||Returns the number of unique values within the group.||COUNTD(City)|
*Calculations with the fields, whose name start with a number are not supported in Zoomdata.
**The term 'group' refers to an attribute (or the Field ID for a column of data that is read by Zoomdata).
Table and Window Functions
Table and Window functions are supported in calculations. The Table function lets you query an entire table as a single dataset. The Window function calculates a metric value based on the selected attribute. However, if the specified attribute is not displayed on the chart, then the function will perform same calculation as the Table function (for example, WindowSum would produce the same result as TableSUM). Table 2 identifies the Table and Window functions that are supported in Zoomdata:
|TableSUM(FieldID)||Numeric||Returns the sum of all the values within the group* across the entire table. This function is used with numeric fields only.|
|TableAVG(FieldID)||Numeric||Returns the average value within the group across the entire table. This function is used with numeric fields only.|
|TableMIN(FieldID)||Numeric||Returns the minimal value of the group across the entire table. This function is used with numeric fields only.|
|TableMAX(FieldID)||Numeric||Returns the maximal value of the group across the entire table. This function is used with numeric fields only.|
|TableCOUNT(FieldID)||All***||Returns the number of values for the specified field across the entire table. When the 'FieldID' is specified, this function ignores fields containing 'null'. If using the wildcard character (*) for the 'FieldID', this function returns the number of records for the entire table, including 'null' fields.|
|TableCOUNTD(FieldID)||All||Returns the number of unique values for the specified field across the entire table.|
|WindowSUM(metric, attribute)**||All||Returns the sum of a metric calculated within the group set by attribute. If the selected attribute is not shown on the chart, then this function returns the same result as TableSUM.|
|WindowAVG(metric, attribute)||All||Returns the average of a metric calculated within the group set by attribute. If the selected attribute is not shown on the chart, then this function returns the same result as TableAVG.|
|WindowMIN(metric, attribute)||All||Returns the minimum value of a metric calculated within the group set by attribute. If the selected attribute is not shown on the chart, then this function returns the same result as TableMIN.|
|WindowMAX(metric, attribute)||All||Returns the maximum value of a metric calculated within the group set by attribute. If the selected attribute is not shown on the chart, then this function returns the same result as TableMAX.|
|WindowCOUNT(field, attribute)||All||Returns a count of records for the specified field confined by the identified attribute. If the attribute is not displayed on the chart, then this function works like the TableCOUNT function. When the 'field' is specified, this function ignores 'null'. If using the wildcard character (*) for the 'field', this function counts all records, including records containing nulls, for each value of the specified attribute.|
|WindowCOUNTD(field, attribute)||All****||Returns a distinct count of records for the specified field confined by the identified attribute. If the attribute is not displayed on the chart, then this function works like the TableCOUNTD function.|
- *The term 'group' refers to an attribute (or the Field ID for a column of data that is read by Zoomdata).
- **Window function requires two arguments: the first should be a metric (such as integer, money or number) and then you can have one or more attribute to group your window function by.
- ***'All' types include attribute, numeric, time and text.
- ****WindowCOUNT and WindowCOUNTD functions require two arguments: the first can be any field and then you can have one or more attributes to group your window function by.
Filtering A Calculation
Filters can be used in calculations. This means that attributes from a dataset can be applied as filter parameters to further refine and narrow down the results. Specifying a filter lets you perform a calculation within a predefined dataset range, for example, determining sales results within a certain period of time or within a certain demographic.
Filtering using the Date function allows you to apply dates and time-specific filters to a calculation. Table 3 identifies the Date functions that are supported in Zoomdata:
|DateADD(time_period, interval, date)||Date formats||Increments the specified date to return a new date. The increment is defined by the date and interval|
|DateSUB(time_period, interval, date)||Date formats||Decrements the specified date to return a new date. The decrement is defined by the date and interval|
|PreviousPeriod(offset, numPeriods)||Time granularity||Returns the period previous as compared to the currently selected period based on a time filter. Granularity (offset) includes day, week, month and year.|
Filtering a calculation in Zoomdata requires knowledge of the operators that are supported and the formula structure needed to build a formula. Detailed information, examples, and step-by-step instructions for creating a filtered calculation are provided in the Applying Filtered Metrics article.
A Calculations Editor tool is available to help you create custom formulas. This tool can be accessed from any data source connected to Zoomdata (that you have access rights to). The Calculations Editor helps you craft custom formulas by providing a list of supported functions, available Field IDs (from the data source), and customized fields (from other calculations).
The Calculations Editor workspace is separated into the following distinct areas (as shown in Figure 4 and described in the Legend below):
|Create a descriptive name for your calculation. Three action buttons are available that let you save, create a new calculation, and close the Calculations Editor .|
The Function Library section shows a list of available functions that can be used in building your calculation.
The Attributes and Metrics section lists all the available Field IDs from the data source that can be used in the calculation, including any custom fields created from other calculations (as shown in Figure 6).
Only Field IDs and customized fields can be used in the Calculations Editor .
The calculation workspace is for creating the formula.
The Preview pane lets you test the calculation you created to ensure validity; results are shown in a Pivot Table. If there are any issues with the formula, descriptive errors messages will display.
Consider taking a little time to plan the calculations needed for your analysis and exploration of the data source. Questions you may want to consider:
What attribute(s) and/or metric(s) do I need as resources for my calculation?
What function(s) do I need to use in my calculation?
- What operators will I use in my calculation?
Syntax and Logical Structure
Zoomdata calculation functionality uses certain syntax and follow a logical structure. In general, creating a formula entails selecting the appropriate function variable, integrating the desired attribute or metric and inserting the necessary operators. Zoomdata calculations uses the following logical structure (as shown in Figure 7):
A comprehensive spectrum of examples is presented below to demonstrate the different calculations that can be created.
To visualize this result, an aggregated function is created to calculate total sales results: SUM(sales) as shown in Figure 9.
As shown in the Preview (in Figure 9), the aggregated function calculates the sum of all the merchandise sold by state and visualized in a Pivot Table. In this example, the logical syntax for a basic calculation includes the aggregate function (SUM) and the metric (sales).
Examples: Aggregated Functions Using Basic Arithmetic Formulas
Determining the percent profit from sales (as shown in Figure 7):
SUM(Profit) / SUM(Sales) * 100
Calculating the difference between actual sales and planned sales:
SUM(ActualSales) - SUM(PlannedSales)
Difference between top and bottom Sales:
MAX(Sales) - MIN(Sales)
Variance presented by defining how min/max value is close to the mean:
Calculating the number of unique items sold in the specific state:
Examples: Table, Window and Date Functions
Calculating the profit for each attribute as a percent of total:
SUM(Profit) / TableSUM(Profit) * 100
Comparing average profit in group against average across the table:
(TableAVG(Profit) - AVG(Profit)) / TableAVG(Profit) * 100
Calculating the sum of sales within the electronics category:
Adding one month from the current date:
DateADD('Months', 1, date())
Subtracting three days from a specific date:
DateSUB('Days', 3, ‘2015-01-01’)
Calculating the total for planned sales between a specific time period (in this case from Jan 1, 2015 to current day):
(SUM(planned_sales) WHERE sale_date BETWEEN '2015-01-01' AND DATE())
Calculating the sales total for the previous time period (current time period (SaleDate) is Jan 1 2016 12:00:00 AM - Mar 31 2016 11:59:59 PM ):
For more complex calculations, Zoomdata supports the use of operators (or expressions). Zoomdata supports a limited number of operators, which follows the SQL syntax/grammar in the creation of logical expressions:
IN, NOT IN
These expressions are most commonly used in Filters. Refer to the article Applying Filtered Metrics for detailed instructions on creating calculations using these expressions.
Step-by-Step Instructions for Creating a Calculation
To create a custom calculation, take the following steps:
. You can access either from a specific Data Source, in the
page (as shown in Figure 10):
You can also click Add Calculation on the chart canvas (Figure 11):
window, specify the name of your calculation in the corresponding field (1).
Build your calculation as follows:
Select the function(s) from the Function Library section (either double-click the function name or enter it manually in the editor; as shown in Figure 12, (2), (4).
Add the attribute(s) and/or metric(s) from the Attributes & Metrics section (either double-click the function or entering it manually in the editor; as shown in Figure 12, (3), (4).
Enter the necessary operators.
See Figure 13 for an example of how to build a calculation in the Calculation Editor.
Click Run Calculation in the Preview section, as shown in Figure 12, (5), to validate your calculation.
- Verify the results in the Preview section.
- Edit your calculation as needed.
- Save the calculation.
If you need to create another calculation, you can click
to clear the workspace and start on a new one.
These custom calculations are then incorporated into the Attributes menu on any charts created based on the data source (as shown in Figure 14).
In order to access the Calculations functionality, your data source must first be successfully connected to Zoomdata and saved. Once the data source appears in the Sources page, you will be able to access the Calculations functionality from different areas within Zoomdata.
The Calculations functionality is located in the Data Sources, Fields page (as shown in Figure 15).
The Calculations functionality is located in the Data Sources, Fields page (as shown in Figure 15).
You create custom calculations in an editor that is accessible from a variety of locations within Zoomdata. Calculations can be created from the chart canvas (as shown in Figure 16).
If there are existing calculations for the data source, you will see a Formula section in the menu along with a New button to the right of the section header.
In addition, when there are existing formulas, you have the option to take the following actions for them:
Edit the formula (opens the Calculation Editor)
Duplicate the formula (automatically creates a duplicate of the formula and displays in this section)
Remove the formula (will permanently delete the formula for that data source)
The Calculations Editor can also be accessed from charts on a dashboard (as shown in Figure 17).