Automate Calculations & Evaluations - Creating calculated series
Fernando Montenegro
41 steps
12 minutes
Clearpointstrategy
Google Docs
1
Start by logging into ClearPoint Strategy at [app.clearpointstrategy.com/#/home](http://app.clearpointstrategy.com/#/home)
Adding a Calculation
2
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**
3
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
4
Actual minus Target: How far ahead or behind are we on our Target? ClearPoint will calculate this for you.
5
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’.
6
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** 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
7
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’.
8
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
9
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.
10
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’.
11
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**\ 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
12
Want an absolute value? Just place your value inside of the ABS( ) tag. The list of tags can be found from the Function dropdown.
13
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’.
14
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 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
15
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.
16
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**
17
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
18
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:
19
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**
20
From the **Calculation Type dropdown menu** select **Calculated Series**\ Use the **Function dropdown menu** to select **CASE WHEN**
21
In this case, we are saying the output should be 0 when the Net Profit is less than $30 million.
22
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’
23
Second ‘WHEN’ statement\ Use **Series** to select **Actual**\ Click on the **Insert button**\ For our second validation enter, ‘< 3000 THEN 2’
24
Third ‘WHEN’ statement\ Use Series to select **Actual**\ Click on the **Insert button**\ For our third validation enter, ‘< 4000 THEN 4’
25
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
26
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.
27
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**
28
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
29
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**
30
You will notice that the Expenses Measure on the Central Division Scorecard has a Null value, COALSCE allows the null value to be treated as a 0 instead.
Exponents
31
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.
32
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**
33
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
34
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.
35
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**
36
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
37
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.
38
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**
39
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.
40
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.
41
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.
Automate Calculations & Evaluations - Creating calculated series
Fernando Montenegro
41 steps
12 minutes
Clearpointstrategy
Google Docs
1
Start by logging into ClearPoint Strategy at [app.clearpointstrategy.com/#/home](http://app.clearpointstrategy.com/#/home)
Adding a Calculation
2
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**
3
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
4
Actual minus Target: How far ahead or behind are we on our Target? ClearPoint will calculate this for you.
5
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’.
6
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** 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
7
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’.
8
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
9
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.
10
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’.
11
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**\ 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
12
Want an absolute value? Just place your value inside of the ABS( ) tag. The list of tags can be found from the Function dropdown.
13
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’.
14
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 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
15
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.
16
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**
17
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
18
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:
19
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**
20
From the **Calculation Type dropdown menu** select **Calculated Series**\ Use the **Function dropdown menu** to select **CASE WHEN**
21
In this case, we are saying the output should be 0 when the Net Profit is less than $30 million.
22
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’
23
Second ‘WHEN’ statement\ Use **Series** to select **Actual**\ Click on the **Insert button**\ For our second validation enter, ‘< 3000 THEN 2’
24
Third ‘WHEN’ statement\ Use Series to select **Actual**\ Click on the **Insert button**\ For our third validation enter, ‘< 4000 THEN 4’
25
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
26
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.
27
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**
28
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
29
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**
30
You will notice that the Expenses Measure on the Central Division Scorecard has a Null value, COALSCE allows the null value to be treated as a 0 instead.
Exponents
31
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.
32
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**
33
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
34
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.
35
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**
36
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
37
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.
38
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**
39
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.
40
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.
41
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.
