Calculations and Aggregations

Webinar 32 in the "I Didn't Know ClearPoint Could Do That!?!" webinar series

When it comes to calculations, we’re betting ClearPoint has even more options than you think. Taking advantage of calculations saves you the time of manipulating raw data, and ensures that you’re seeing the correct numbers each month or quarter. Ready to learn more?

Check out this episode of the “I Didn’t Know ClearPoint Could Do That!?!” Webinar to dive into basic and advanced calculations, aggregations, and an awesome new function of ClearPoint 11 – aggregate status series.

Happy reporting!

 

Webinar 32 Transcript

Introduction (0:00)

  • Hello everyone and welcome to the “I didn’t know ClearPoint could do that!?!” webinar series
  • Today, we’re showing you the ropes of setting up calculations and aggregations in ClearPoint.
  • Before we get started, here are your smiling hosts, I’m Catherine
  • As a few housekeeping announcements:
    • We will be recording this session and will make it available within a week
    • Webinar will last about 25 minutes with time for questions
    • Submit questions to [email protected] (answered at the end)
    • We’ll follow up via email
  • On our last installment, we dove into our Spring Cleaning eBook to erase clutter from your account. I’d highly recommend checking out the recording on our blog, support center, or Vimeo channel – just search ‘webinar’.
  • Today, we’re talking about calculations and aggregations. These automations let you bypass entering data by hand, so that you can quickly and reliably get the numbers you need.
  • Not only does this save you that data entry time, but you also save time debating whether numbers are right. That way, you can get right to analyzing the meaning behind the numbers.

 

Agenda (0:48)

  • We’ll show you how to create commonly used calculations in ClearPoint
  • Then we’ll build on those calculations to some more advanced options,
  • We’ll dive into aggregations,
  • And show you a function of ClearPoint 11, aggregate series

So, that’s more than enough intro, and we’ve got a lot to cover, so let’s get started. Per usual, we’re flying the friendly skies at upward air.

 

Basic Calculations (1:32)

     Percent of Target (1:32)

  • Now, you might as well call this Throwback Thursday, because we’re taking it way back to fourth grade math class with some simple division.
    • I’m going to hop over to the in the Eastern Division and go into my Revenue measure,
    • And here in the data table I have my Actual revenue series, and Target revenue.
    • Maybe I want to see my actual revenue as a percentage of where we expected to be.
    • So, I’ve set up a series here called % of Target, and in order to edit that series, all I need to do is double click on the header.
      • From here, I’m going to navigate right over to the Calculation tab.
      • This tab is going to become your best friend today.
      • You might even call it… the calculation station.
      • And we’ve got everything we need here to start building our calculation. As we build it, it’s going to appear here in this panel.
      • Neil: ClearPoint’s calculation engine is really powerful, and you can pull data from any series in ClearPoint into any other series in ClearPoint.
        • Neil: That’s why we have these dropdowns to specify where the data is going to come from.
      • Catherine: Yup, good point. And we’re going to start off really simple. So let’s start with referencing the Actual series in the Revenue Measure.
        • So we’ve got the Upward Airlines Corporate Scorecard selected here, and the Revenue measure,
        • And we’ll want to go and select a series from the third dropdown. So I’ll choose Actual.
        • The last dropdown, Aggregation, is the period whose data you are referencing. The option that pops up here is Current Period, meaning that, whichever period the data is shown in, is the period that will be referenced.
          • Neil: So in November, for example,% of target will show the November actual revenue divided by the november target revenue.
        • Catherine: Now, to put that value into the calculation, I’m going to click the Insert button, or the Harry Potter button, if you’re a Harry Potter fan
          • Neil: We do sometimes refer to this page as the calculation wizard.
      • Catherine: And we’ll see a token magically pop up in the Calculation panel.
      • So let’s deconstruct this quickly. What you see here first is a unique ID that tells ClearPoint which measure this is, and then you can see the series name, and that we’re pulling in the Current period.
      • Onto the division. You can use your keyboard, but we’ve also got handy operator keys here to save you the trouble. So we’ll click the forward slash division sign, and see it appear in the calculation panel.
        • Now this time we’re going to go insert the Target series.
        • Same Scorecard, Revenue Measure, choose the Target series, current period, and click Insert.
        • Not so bad!
        • Now lastly here, for our percentages in ClearPoint, we’re going to want to multiply by 100. That’ll turn .43 into 43%.
        • Neil: And let’s hope we’re hitting a better percent of target than 43 percent.
        • Catherine: There you have it!
          • The % of Target series is greyed out, which tells you that the information is calculated.
          • And we can see how close we got to hitting our target for each month.

   Year To Date (5:09)

  • Neil: Now, so far, we’ve just worked with values that are all from the same period, but we did promise that our aggregation dropdown did more than just that.
    • Catherine: Right, let’s take a closer look. I’ve got another blank series in my data table, the YTD Actual, which is our total revenue up to this point in the year.
  • Again, to edit, I’m going to double click on my YTD Actual Series header.
    • And hop back over to Calculations
    • So into the dropdowns, we’re going to use the actual series.
    • Then, instead of current period, I’ll click into my Aggregation dropdown, and wow, look at how many options we have here.
    • So, we can do all manners of references, whether it’s a specific period, previous year period, or an average or sum, all with just a couple of clicks.
      • Now, I’m going to choose Calendar YTD Sum, to show how much revenue we’ve built up over the year.
      • So I’ll click Insert, and you can see in our token that we are now referencing calendar year to date sum
      • And that’s our whole calculation – I can click save.
      • Our revenue builds up month after month, and then when we get to January, that calculation starts over.
  • Neil: That’s much easier than adding these values by hand. Now, so far, we’ve only just looked at the revenue measure, but there are plenty more measures in our account, and we can use any of them, in any combination, in our calculations!
  • Catherine: If you took all the possible calculations you could perform in this account in ClearPoint and laid them end to end, they would stretch to the moon and back.
  • Neil: It’s true, we’ve done it. There are some ClearPoint calculations floating around the space station up there
  • Catherine: yeah, it’s almost like a… calculation constellation.

Referencing other measures (7:04)

  • Alright, let’s take a look at our Net Profit measure.
  • Now net profit calculated by subtracting our expenses from our revenue.
    • Neil: So do we have to enter revenue and expenses AGAIN in the net profit measure to calculate that?
  • Catherine: Heck no! That would be a waste of time, and wasting time is not what ClearPoint’s about.
    • We’ll set up a calculation to do this for us.
    • Let’s double click on our actual series > back to the Calculation tab.
    • Even though we’re looking at the Net Profit measure, I’m going to choose to reference information from Revenue.
    • We’ll pull in the actual series > for the Current period
    • Now, we’ll subtract, and we’re going to subtract data from our Expenses measure instead.
    • Expenses measure > Actual Series > Current period
    • Now, we’ll save – and we can see our net profit without entering any data manually into this measure!
  • There’s one more really important example where referencing other measures can save you a ton of time.
    • Now, say that you have several measures that are dependent on the same numbers.
    • Neil: For a city, this number might be population, and various subsets of that population.
      • Neil: You need it to calculate crimes per 100,000 residents, voter turnout, unemployment rate, greenhouse gas emissions per capita, the list goes on.
    • Catherine: For our airline, the total number of flights is a big one.
      • We calculate % of on time flights, maintenance cost per flight, and more.
    • So, how can you ensure that all those measures are referring to the same total flights data?
    • We could spend time debating the numbers that were used, or we could just add a Reference Measure to ClearPoint.
    • Let’s go to our Reference Scorecard, and click on Total Flights.
    • Here we have have a central number for total flights in the Eastern, Central and Western division, as well as the sum of all three (Total).
    • As long as users have access to this scorecard, they can reference it in any other measure series in any scorecard.
    • And you know there’s one central place you can go to see the number being used.

 

Advanced SQL Calculations (10:18)

  • Alright. We’ve covered some basic operations, now, let’s dig into some more advanced calculations.
  • ClearPoint lets you move beyond those basic operations and use certain SQL functions for absolute value, exponents, and more.
  • Neil: This may sound daunting, but don’t worry – you don’t need any prior experience with SQL to become a pro at calculations in ClearPoint.
    • Catherine: That is true. So. I’m going to go back to my Revenue measure.
    • Maybe we want to set up a calculation where the end result should be different based on various scenarios. A sort of if/then statement.
      • Looking at my YTD Actual calculation, I see that it populates even when there is no number in the Actual series. We might panic if we look ahead at December 2018 and think that we only made $8000.
      • It would be better to say that IF there’s a value in the Actual series, THEN we display the YTD. Otherwise, we leave it blank, or null.
      • So, let’s make that change to our YTD Actual statement. I’ll double click on the Actual header, and over to the Calculation tab.
      • I’m going to take out our existing calculation for now.
      • Now, in SQL, an if/then statement is called a CASE WHEN calculation
        • we’ve actually got a handy tool built in to set this up.
      • I can start my calculation by clicking the Functions dropdown, and clicking CASE WHEN.
      • And we have two scenarios, or cases.
        • In one, the actual series is null, and in that case we want the YTD to be null too.
        • In the other, the actual series has a value. And in this case, we want to show the Actual YTD.
      • So let’s set up the first case. This is WHEN the actual value for the current period (which we’ll pull in from the dropdowns), is null. So we’ll insert that value and write out, “IS NULL,”
        • We’ll type THEN to specify what should happen.
        • We want this series to be null too. We’ll write “THEN NULL.”
        • There. No Actual value, then no Actual YTD value.
      • Then, we want to define what will happen otherwise. So, we go back to the function drop down and type ELSE.
      • In our other scenario, when Actual has a value, we want to show the Actual YTD Sum. So we’ll use the dropdowns to insert Actual > YTD sum and click the Insert button.
      • And then lastly, we do need to END the calculation, to show that there are no more options.
        • We can go into the function dropdown, and choose END.
      • Let’s save it and see! Now with our one calculation, we only have YTD data when there’s actual data.
    • Okay, so let’s combine everything we’ve done so far into a super-sleek calculation for our Lost Time Incident Rate. And I’ll go to that measure now.
    • So our Lost Time Incident Rate is the amount of lost time due to injury and illness each month.
    • Neil: So obviously, we’re trying to keep this number as low as possible.
    • Catherine: Right, not only do we want to have records of LTIR each month, which we have here, but we want to be able to determine the BEST case scenario for the end of the year – that is, the end of year average if there were NO additional lost time for the rest of the year.
    • Now, it’s hard to calculate that average when there’s no data for future periods in the actual series.
    • But we can get around that by creating a new series where the null periods are shown as zeros. That way, we can incorporate all those periods into a best-case average for the end of the year.
    • So in my Future Zeros series, I’ll double click, and set up a new case when.
    • So similar to our last calculation, we’ll have a CASE WHEN the actual series “IS NULL”, but in this case, we want the series to display zero – THEN 0.
    • Otherwise, “ELSE,” we’ll pull in the current period value for the actual series.
      • And, END the calculation.
    • Alright, now there’s one more layer to this that will allow us to show the best case scenario for the end-of-year monthly LTIR average.
      • Now, we can’t change our past data, but every month we’re aiming towards zero. And, of course, our Future Zeros shows what our data would look like if that was the case.
    • So, we’ll use the Best Case Scenario series to show the YTD average from the Future Zeros column. We’ll double click on the Best Case Scenario header, and over to the Calculation tab.
    • We can use our dropdowns here, and select the Future Zeros series, with a Calendar YTD Average aggregation.
    • So we’ll insert, save, and see what our data would look like each month if we had no incidents for the rest of the year.
    • Neil: Now, we’re really interested in the best case scenario for the end of the year. Which, since we’re looking at Feb-18 is going to be the December 2018 period from the Best Case Scenario series.
      • Catherine: Right. So, what I’ll do, is use that last series we have here to showcase just the December value. I’ll double click into EOY best case,
      • Over to calculation station, and we’ll insert the Best Case Scenario series, for a particular period, the Dec-18 period.
      • Then, when we get to the end of 2018, we’ll want to replace this period with the December 2019 value.
      • And let’s click save.
    • Great, so we can now see what the end of year monthly average will look like if we are able to avoid any incidents for the rest of the year!
      • If we wanted to, we could hide the intermediary series we created from the data table, by double clicking, and checking the box next to Hide series from data tables and hide series from summary reports.
  • Now, this is just a taste of all that ClearPoint’s calculations can do. But hopefully we’ve provided some helpful building blocks to create the calculations you need.
  • And if you’re stuck on entering a particular calculation into ClearPoint, well, that’s what our support team is for.
    • Neil: Exactly. We’re here to help. I have a question for you though… what’s that lobster? And why is he surrounded by math symbols? Wait… Oh. Oh no. don’t answer that.
    • Catherine: He’s the calculation crustacean!
    • Neil: Wow, this is embarrassing. Did you make that?
  • Catherine: And with that, it’s time to shift the calculation conversation over to Neil to talk about aggregations.

 

Aggregate Series Calculations (19:00)

   Aggregate Series Sum (19:00)

  • Thanks Catherine, for walking us through some of the calculations that ClearPoint can run.
  • There’s a different type of powerful calculation in ClearPoint that we haven’t covered yet, and those are our Aggregations.
    • Aggregate series are a powerful way to quickly add or average data across multiple departments or divisions. Think of it as the “SUM all” function of ClearPoint.
  • For example, if I click into Revenue in the Corporate scorecard, I can see that there is a Revenue measure in each one of our divisions.
  • Clicking into the Eastern Division, I’ll see a data table with multiple series, including an Actual and Target.
  • The account is set up so that all the revenue measures have these Actual and Target series.
  • Now, there are two ways to calculate the sum of all of the divisional revenue measures in the Corporate Scorecard.
  • So, I am going to click into in my Corporate Revenue Measure, double click on the actual series, and into the Calculation tab,
    • We’ll see that it I’ve inserted the Actual values into my calculation and added them one by one,
    • But there’s an even easier way to perform this rollup if I use the ‘Aggregate Series’ function.
    • I’m going to walk you through that second option!
  • The benefit here is that aggregate series are dynamic, whereas regular calculations are not. What this means is that if we add an additional division, our criteria would adjust without having to add it to the calculation manually.
  • From the Series Calculation Type dropdown, I am going to select Aggregate Series
    • What this allows me to do is pull in measures from throughout the system and perform map operations on them.
    • Catherine: Right, so we’re specifying criteria for multiple series here, rather than individual series.
  • Neil: Let me walk you through how it will be set up.
  • First, I would determine which scorecards I want to pull from. So, in this example, I want to pull from all children of Upward Airlines Corporate –
    • See how the number on the right went to 3. This tells me there are 3 child scorecards that fit this criterion.
  • Next, I need to clarify which measures I want to pull from.
    • Since I know each Divisional scorecard has a Revenue measure, I choose ‘Measure name equals’
      • There are other options, however, and those are:
        • ‘All measures’,
        • ‘Child Measures’ of the current measure you are on, in case you have the measures set up in a parent-child format, or
        • ‘Measure name contains’, if you don’t have precise naming convention across all divisions.
    • Next I can designate the name in the ‘Measure Name’ line
    • I will enter measure name as ‘Revenue’.
  • Since I also know each series name is Actual, I will go to the ‘Series Name Match Type, and choose ‘Series name equals..’ and then designate which series to pull from in the ‘Series Name’ line
    • Catherine: So because we chose “series name equals”, they do all have to have the same name in order to be included in the aggregation.
  • Neil: Yep, exactly. So I am going to type in ‘Actual’.
  • Finally, you decide whether to sum, average, count, or take the standard deviation. Since we’re finding total revenue, we want to sum the values from each series.
    • Additionally, I can choose to ignore blank values or I can treat the blank values as 0’s. So unlike in a standard calculation, blank values won’t prevent our sum from appearing!
    • Catherine: Neil is referring to the fact that a standard calculation will only appear if all values referenced contain data. And this does have its advantages in some situations, especially if you need to make sure you’re looking at a complete set of data before determining that final value
  • Neil: That’s a good point. For now, I will go ahead and hit ‘Save’.
  • And there you have it, we now have an aggregation sum that is pulling in information from each child Revenue Measure!

 

Aggregate Series Average (22:53)

  • Now, we can move along and talk about aggregations using averages, which is a second capability of the ‘Aggregate Series’ function.
  • For this example, we are going to be in the in Upward Airlines Corporate scorecard again, but on a new measure so, I will navigate to the Expenses measure.
  • I can see that there is already an Actual series calculating the Sum of Expenses from the three divisions.
  • Say I am in the Corporate budgeting office, and I also want to calculate the average expenses across each division.
  • I am going to double click the data table, create a new series, and call it ‘Divisional Avg.’
    • If we navigate to the Calculation tab, we can follow nearly all of the steps from the previous ‘Sum’ Example.
    • For this example, I want to pull in measures of children of Upward Airlines, and ensure that the measure name equals ‘Expenses’.
    • The series name is still going to need to be ‘Actual’.
    • However, this time, instead of running a sum calculation, I am going to calculate the Average.
    • This is where the treating blank values as 0 or ignoring the blank values comes into play.
      • With a standard calculation, I’d have to specify that I’m dividing the sum by 3 to get the average, because there are three divisions. So if there were blank values at the division level, it would throw the average off.
        • But with aggregations, I can choose to ignore blank values, and ClearPoint will adjust – so if I only two divisions have entered in data, the sum will be divided by two.
      • Then, we’ll hit ‘Save’.
    • Now we can see, across our city offices what the average expenses are across the organization.
    • I could be missing some values, but I will still be getting a value returned to me at the corporate level from the divisions who have data available.
  • Now, say you are a member of a division and you want to see how your specific department compares to the Corporate Average. Since we just created this aggregate series function in the Corporate Expenses measure, all we have to do is reference it using the calculation wizard that Catherine outlined!

 

Aggregate Status (25:00)

  • So, that was a good introduction to the capabilities of Aggregate Series.
  • Now, let me walk you through a new calculation type in ClearPoint, called ‘Aggregate Status’
    • Aggregate Status lets you automatically count the number of elements that have a particular status within the criteria you determine. Think of it as the “SUM all” function of your elements’ statuses!
    • This is particularly useful for project management, to see how you’re progressing on initiatives and action items. Let’s show how exactly this feature works!
  • So, I have a measure created called ‘Initiative Status Snapshot’ in the Corporate Scorecard. With this naming convention, it’s clear that by looking at this measure, I’ll be able to see a high-level overview of how we are doing on our initiatives.
    • Our measure is tracked monthly, but maybe yours will be quarterly, depending on how your organization tracks projects.
    • If I move to the data table you can see that I have 5 series, one for each status, and one to count all Initiatives with statuses
    • The Green, Red, and Yellow series have already been built out, but I am going to walk you through how to do so with the ‘Completed’ Series
    • So, if I click into ‘Completed’ series on the measure data table and click into calculation tab, I will be able to set up the aggregation
      • Now, the first thing I am going to do is click the dropdown for Select Calculation Type.
        • Let’s use the last option, Aggregate Status.
      • For this example, I only want to look at the Corporate Scorecard, so I am going to select ‘Specific Scorecards’ and choose Upward Airlines
      • Next, I know that I want to look at all of the Initiatives, so I will choose ‘Initiatives’ for my Element Type, and clarify that I want ‘All Elements’ counted.
      • Finally, since this is the Completed measure series, I am going to clarify that the status should equal ‘Completed’.
      • Now, I can hit ‘Save’.
      • And as you can see from the data table, in February of 2018, we have 5 projects that are labeled as Completed.
    • Now, I am going to repeat these steps in a similar manner for the Total Series
      • The only difference, as you will note, is going to be changing the Status that we want to count! It’s that simple.
      • So, I am going to double-click into ‘Total Initiatives with Statuses’, and follow the same steps I just walked through.
      • This time, however, I am going to select each status for the ‘Status Indicators’ field, which will sum each Initiative that has been given a status, besides ‘No Information’.
    • After I do this, I have a nice, easy to comprehend data table.
    • Catherine: Yeah, this is great! And the cool thing about aggregate statuses is that once you’ve aggregated the numbers, you can use them just like other series in standard calculations. So we could divide one status series by the total to find the percentage of initiatives that are on target.
    • Neil: Exactly! And there is one chart that we have found particularly useful when looking at Projects, and that is the pie chart.
      • You might have only thought of your charts as being useful for measure or metric data—but this project status pie chart helps monitor a variety of initiatives as they relate to the overall strategic plan.
      • For example, if you have 100 initiatives and they all each have their own status, this pie chart shows you how you’re doing collectively in the project world at a particular point in time.
    • So, I am going to double click into this blank chart here.
      • First, I will designate the ‘Chart Type’ as the Pie Chart;
      • I am also going to remove the chart legend, because each series name will show up next to its respective slice on the pie chart.
      • Second, I will go to the ‘Chart Periods’ tab and select ‘Current Period’ for Periods to display.
        • This is because a pie chart can only show one period at a time, so it makes the most sense to have it display only the current period, whether that be a month, fiscal year, or quarter.
      • Finally, I am going to go into the Chart Series Tab to designate what I want charted.
        • I will select each series so I have a high-level view of how we are currently performing.
        • As you can see, I have already clarified what color each series is going to be according to the series name, for simplicity sake.
          • However, feel free to use any color convention your organization uses!
      • Now, if I refresh the preview, I will see how the chart will appear.
      • All I have to do now is hit save, and we will see the chart on our detail page.
  • So this whole example was done for initiatives, but we all know that many projects involve action items as well.
    • I could easily duplicate this measure and adjust my Aggregate Status calculations to show Action Items,
    • However, for the sake of time, we have an Action Item’s Status measure already built out, and it has a chart identical to the one I created just a few moments ago.
    • Now that we have these two status snapshot measures built out, we have a solid overview of how our projects are currently doing!
  • And, we have a cool way to take this a step further, and that is to create a project dashboard.
    • Again, for the sake of time, we already have one built out and I can walk you through it right now!
      • Here we are referencing both of our aggregate status charts to see a high level snapshot of our progress;
    • Then if we scroll to the bottom, the Projects Dashboard references an initiative report to show who owns each project, the end date, and the milestones needed to complete each one.
      • Of course, you can customize the columns in your account to show any important information you want to see.
    • Scrolling back up, this Action items report is filtered to show only the action items owned by the ‘Current User’, which means that each user will have a personalized view of action items they are responsible for.
    • Overall, this assists in the progress and management of your strategic projects and tasks!

 

Catherine: This is awesome! Who knew measures could be so useful for project management in ClearPoint. I know we just gave a high-level overview of this layout, so if you have questions about how to set up an identical report, we’d be eager to help!

 

So, we’re reaching the end of our time here today, but I do want to answer one important question that’s come in.

Questions (31:21)

  • How can I do a Fiscal YTD Calculation. How will ClearPoint know what my Fiscal Year is?
    • One observant attendee must have noticed that there were aggregations in our Aggregation dropdown based on Fiscal Year.
    • You can use these just like Calendar Year aggregations, but you do need to have fiscal years set up in ClearPoint.
    • Administrators can do this under System Settings > Reporting Periods > and then the third toggle option, Fiscal Years.
    • We’ll see here that our fiscal year is July to June, but we only have them set up through June of 2017.
    • That means our Fiscal Year aggregations would stop working after June of 2017.
    • So we can just click to add another, ClearPoint will base the dates off the existing fiscal years, which is nice, and then let’s click Save.

 

That’s our time for today, Thank you so much for joining us on this episode of the I Didn’t know ClearPoint could do that webinar series, we’ll see you next time and Happy Reporting!

Download: 39 Clues To Know You've Outgrown Excel and Powerpoint For Reporting

EXPLORE MORE VIDEOS