How To Create Custom Metrics

To display the metrics in the dashboard, you must both have the Chart of Accounts (accounting.xlsx) and dashboard-config (dashboard-primary.json5 and/or dashboard-customer.json5) files set up. These can be accessed from /private/dashboard folder of your account’s WebDAV server. If you do not have the accounting.xlsx file set up please see Chart of Accounts Excel File to get started.

Sample Dashboard With Metrics

Chart of Accounts Excel File

To begin, please locate the accounting.xlsx file within WebDav. Within this file, you will find the __METRICS__ sheet tab as shown in the image below. Here you will see the following columns: Metrics (column A), Label (column B), and Formula (column C). In the sample accounting.xlsx found under Chart of Accounts Excel File, you will see the same sample metrics and accompanying formulas as in the image below.

Below is an image of a populated Chart of Accounts sheet. Since the variables have values, the formulas return a value.

Columns In __METRICS__ Tab and Descriptions

Which Variables To Use For Formula Creation

The variables come from the accounts listed in the Chart of Accounts (COA), which can be found in the CUSTOM_ID column from the __MAPPING__ sheet of the same Excel file. Those accounts have identifiers, which are the variables referenced in the formulas. In Excel terms, it works as if you would be referencing a cell as an argument in the formula, but you're using those custom ids instead.

If you are unsure which account a Custom ID refers to, you can check the TYPE_ID and HEADER columns. Additionally, if you want more information about how the COA works, visit  Business Financial Template Excel File.

Now, an example of how to create a custom metric. Let's say I want to build a metric that returns a series of cash and bank accounts. Since we already know that the mentioned account's Custom ID is CASH_AND_BANK, we could do it like this:

Moreover, you can build metrics using other custom metrics as variables. In the example below, the metric Cash/EBITDA  uses the ebitda custom metric as a function argument. Please make sure the custom metric is in capital letters when adding it to a custom metric formula and to save your changes before requesting the metrics API endpoint.

Using Data from Previous Periods in Formulas

You can build metric formulas using information from previous periods. All you have to do is concatenate the unit (_M for “month” and _Y for “year”) and the period of time (an integer with 1 as the minimum value allowed) to the account’s Custom ID.

For example, to create an n-months moving average formula for cash and bank, the syntax would be:

CASH_AND_BANK_M1 + CASH_AND_BANK_M2 + … + CASH_AND_BANK_Mn

The screenshot below contains an example of a 3-months moving average metric for CASH_AND_BANK:

List of Global Variables

Here you can find a list of some variables that are not explicitly listed in the __MAPPING__ sheet but can still use them to create metric formulas.

List of Default Metrics

There is no exhaustive list of metrics since they can be custom defined. If you’re looking for a metric you don’t see here please contact support as we have additional metrics libraries available.

How to Debug and Visualize the Calculations on Excel

It's possible to download the calculations for the metrics in an Excel file through the private API for the metrics. To do that, you must make a request to the metrics private API with a download parameter set to true. It should look similar to this:

https://{subdomain}.{domain}/apps/dashboard/data.php?account={account_key}&item=metrics&download=true

Where account_key is the account key of the customer's account you're calculating the metrics for. This will start a download. In this file, there is a sheet named __METRICS__,  where the calculations will be and look similar to this:

How to Add Metrics to Dashboards

To add your custom metrics to dashboards:

1. Open the dashboard configuration file on your account's WebDAV /private/dashboard folder (dashboard-primary.json5 for the main portal or dashboard-customer.json5 for the customer's portal).

2. The file should have a key named metrics, which holds an array of strings (you can include it in the file if it's not there). Add the metrics' ids you'd like to be displayed in the dashboard as metric cards. Please list the elements/metrics in the order you would like them to display on the dashboard (left to right). It should look similar to this: 

   CODE

   {	
   ...
     metrics: [
   		'debt_asset_ratio',
   		'current_ratio',
   		'yoy_sales_growth',
   		'debt_service_ratio',
   		'working_capital',
   		'months_liquidity',
   	],
   ...
   }

   You may also look at an example completed configuration file.

3. After making the changes, save the file. When you visit the dashboard, you should see the metric cards displayed at the top of the page:

   

Completed Dashboard Configuration File

To see a sample of a completed dashboard configuration file, please see here.