Calculated series in ClearPoint is a powerful tool that can help you and your team save a lot of time by automating different types of calculations like Variance, Year-to-date, Average, Percentages, and many more.
Adding a Calculation
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘% of tickets with direct route’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Actual’.
Navigate to the Calculations tab
From the Calculation type dropdown menu select Calculated Series
Note: Calculations will not populate for a period that is missing the necessary data. To learn how to create a series that adjusts for this and calculates even when the entered value is null, read the sections below.
Types of Calculations
Basic Variance
Actual minus Target: How far ahead or behind are we on our Target? ClearPoint will calculate this for you.
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Target’.
Navigate to the Calculations tab
From the Calculation Type dropdown menu select Calculated Series
Use Series to select Actual and click on the Insert button
Insert the Minus sign with your keyboard or use the onscreen icons
Use Series again to select Target and click on the Insert button
Once you are done with your calculations, click Save
Year/Quarter to Date Average/Sum
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Actual’.
Navigate to the Calculations tab
From the Calculation Type dropdown menu select Calculated Series
Use Series to select Actual
From the Aggregation dropdown menu select Calendar YTD Sum
Click on the Insert button
Once you are done with your changes, click Save
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.
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Target’.
Navigate to the Calculations tab
From the Calculation Type dropdown menu select Calculated Series
Use Series to select Actual
Click on the Insert button
Insert the Division sign with your keyboard or use the onscreen icons
Use Series to select Target
Click on the Insert button
Insert the Multiplication sign with your keyboard or use the onscreen icons
Enter ‘100’ with your keyboard as we are working with Percentages
Once you are done with your changes, click Save
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.
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Target.
Navigate to the Calculations tab
From the Calculation Type dropdown menu select Calculated Series
Use the Function dropdown menu to select ABS( )
Insert the rest of your absolute value equation inside of the parentheses
Use Series to select Actual
Click on the Insert button
Insert the Minus sign with your keyboard or use the onscreen icons
Use Series to select Target
Click on the Insert button
Once you are done with your changes, click Save
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.
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Target'.
Navigate to the Calculations tab
In this example, we are summing "% of on time departures" from the Eastern and Central Divisions.
From the Calculation Type dropdown menu select Calculated Series
Use Scorecard to select Central Division
Under Measure choose "% of on time departures"
Use Series to select Actual
Click on the Insert button
Insert the Addition sign with your keyboard or use the onscreen icons
Use Scorecard to select Eastern Division
Under Measure choose "% of on time departures"
Use Series to select Actual
'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:
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Target'.
Navigate to the Calculations tab
From the Calculation Type dropdown menu select Calculated Series
Use the Function dropdown menu to select CASE WHEN
In this case, we are saying the output should be 0 when the Net Profit is less than $30 million.
Time to build our first ‘WHEN’ statement
Use Series to select Actual
Click on the Insert button
For our first validation enter, ‘< 2000 THEN 0’
Second ‘WHEN’ statement
Use Series to select Actual
Click on the Insert button
For our second validation enter, ‘< 3000 THEN 2’
Third ‘WHEN’ statement
Use Series to select Actual
Click on the Insert button
For our third validation enter, ‘< 4000 THEN 4’
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.
Once you are done setting up 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 two 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.
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Actual’.
Navigate to the Calculations tab
From the Calculation Type dropdown menu select Calculated Series
In the Calculation field, type in COALESCE( ,0)
In the space before the comma is where you will insert the series token
Under Scorecard select Eastern Division
Under Measure select Expenses
Under Series select Actual
Click the Insert button
Insert the Addition sign with your keyboard or use the onscreen icons
Type in COALESCE( ,0) to sum the second Series
Under Scorecard select Central Division
Under Measure select Expenses
Under Series select Actual
Click the Insert button
Click on the Test button
You will notice that the Expenses Measure on the Central Division Scorecard has a Null value, COALESCE allows the null value to be treated as a 0 instead.
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.
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Target’.
Navigate to the Calculations tab
In the Calculation field, type in POWER ( , )
In the space before the comma is where you will insert the series token followed by your exponent
Use Scorecard to select Upward Airlines Corporate
Under Measure choose "Revenue"
Use Series to select Actual
Click on the Insert button
In this example we are going to enter exponent ‘2’ after the comma.
Once you are done with your changes, click Save
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.
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
Double-click on the Header of the series you would like to add a calculation to, in this example, ‘Target’.
Navigate to the Calculations tab
In the Calculation field, type in ROUND ( , )
In the space before the comma is where you will insert the series token followed by the number of decimal places to round to.
Use Scorecard to select Upward Airlines Corporate
Under Measure choose "Revenue"
Use Series to select Actual
Click on the Insert button
In this example we are going to enter ‘2’ after the comma.
Once you are done with your changes, click Save
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.
Select Scorecards & Elements from the Control Panel
Choose Manage Elements
Open Measures
Select the Measure you are going to be working with, in this example, ‘Revenue’.
To access Calculations Diagram, right-click on the series in a data table and select View Calculation Map
In the window that pops up, the original calculated series and all the series that contribute to the calculation will be there. To see how the calculation is set up, you can click the View Calculation button next to the value.
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, click the Rotate Map button.
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 at the top tells you what kind of element it is
You can also see the Element name and that Element's home Scorecard in the parentheses next to the name.
Click the Element name to open that Element's Detail Page in a new tab
The down arrows 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.