Creating calculated series
Want ClearPoint to do your math for you? Set up automatically calculated series to save you and your team valuable reporting time. In this example we will show a basic calculation.
In This Article
Adding a Calculation
Navigate to your Measure Data table and double click on the header of the series you would like to add a calculation to. In the new window that appears, click into the Calculation tab. Select Calculated Series for the Series Calculation Type.
Types of Calculations
Basic Variance
Actual minus Target: How far ahead or behind are we on our Target? ClearPoint will calculate this for you.
Year/Quarter to Date Average/Sum
Want to keep a running average or sum of the current quarter or year? ClearPoint can easily calculate this value for each period for you.
Percentages
Users who have issues with percentage calculations usually run into the same issue: forgetting to multiply the end result by 100. See how these are constructed below.
Absolute Value
Want an absolute value? Just place your value inside of the abs( ) tag. The list of tags can be found from the Function dropdown.
Insert the rest of your absolute value equation inside of the parentheses.
Rollups
If you have multiple departments or divisions that roll up into an enterprise level scorecard, it is actually quite easy to create a rollup calculation that aggregates all of this data automatically.
Use the Scorecard and Measure selectors to find the appropriate series. Click Insert to add each series to the calculation. In this example, we are summing “% of on time departures” from the Eastern, Central, and Western Divisions.
‘Case When’ / ‘If Then’ Expressions
Like logic? ClearPoint calculations can be configured with CASE WHEN expression to construct the ‘If Then’ logic we all learned in school. It looks a little complicated, but just remember that all we are doing is saying ‘if 1, then do x, if 2, then do y’ etc.
Let’s say we wanted to automatically calculate our employee bonuses based on the Net Profit for the quarter using the following table:
Go to the Calculation tab. Begin by selecting CASE WHEN from the Function dropdown.
Next is your first ‘WHEN’ statement. In this case, we are saying the output should be 0 when the Net Profit is less than $30 million.
Use the Insert button to add your series.
Finish the statement with a >,<,=, etc. and the ‘THEN’ instructions should that criteria be met. If you are comparing the statement to NULL, use ‘IS’ instead of a mathematical operator like =. If none of the criteria are met, you can choose to end the statement with ‘ELSE Null’ to return a blank cell.
Ensure that you enter End from the Function dropdown once you have written your criteria. Click Save.
COALESCE
Want to avoid null values? This is done using the COALESCE expression, which evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. One common example is when you want a calculation to sum up divisional or department values. For example, if we want a Corporate ‘Expenses’ measure that sums up the three Division Expense measures, but one Division is NULL for a given period, the period will show as NULL at the Corporate Level. To avoid this, we can use COALESCE.
In the Calculation field, type in COALESCE( ,0). In the space before the comma is where you will insert the series token. So, ordinarily, you’d sum three series like A+B+C. Now, it becomes COALESCE(A,0)+COALESCE(B,0)+COALESCE(C,0).
In this example, you can see that the Western Division is NULL, which would ordinarily leave the entire series blank. However, with the structure mentioned in Step 1, the calculation knows that if the referenced series is NULL, pull in a 0, and if it is not NULL, pull in the series value. This allows the calculation to run by treating the blank value as a 0.
Exponents
Want to include an exponent in your calculation? You can do so by using the POWER() function. The POWER function works like this: POWER([insert series here],[insert exponent here]). The first value in the parenthesis is your base value. The second number is your exponent.
Round
Want to round a number to a certain number of decimals? You can do so by using the ROUND() function. The ROUND function works like this: ROUND([insert series here],[insert number of decimals here]). The first value in the parenthesis is your value to be rounded. The second number is the number of decimal places to round to.
Calculations Diagram
Calculation Diagrams help visualize the context of a calculated series across multiple layers, including status, value, and calculation of every series that impacts the original calculation.
To access Calculations Diagram, right-click on the series in a data table and select View Calculation Diagram. On the window that pops up, the original calculated series and all the series that contribute to the calculation will be there. To further investigate one of the connected series, click the plus icon to expand. To see how the calculation is set up, you can click the monitor preview button.
Alternatively, if you are in the edit modal for the series, you can access the Calculation Diagram from the Calculation tab.
With the Calculation Diagram, there are multiple features that you can use to help you see the big picture strategy. Use the search bar to highlight specific elements that you are looking for within your diagram. To see the Calculation Diagram at a different angle, check the Rotate Map box. Click the max out icon to see the full diagram. Click the magnifying glass icons to zoom in and out across the Calculation Diagram.
Every element tile has some useful information on it. The element icon in the top left tells you what kind of element it is. You can also see the status for the current period, as well as the element name. At the bottom of the tile, the element’s reporting frequency and current reporting period are listed.
In the middle of the tile, you can see the element’s home scorecard. The preview icon will show you the calculation itself. Click the view icon to open it in a new tab. The plus/minus icons can be used to open and expand that element’s links for further exploration.
There is more than meets the eye when it comes to ClearPoint calculations. In addition to basic math operations, calculations can aggregate annual results or be based on certain SQL functions. Learn about some of our favorites below.