Calculations and Aggregations (Webinar 32)

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. Now before we get started, here are your smiling hosts. I'm Kathryn.

And I'm Neil.

And as a few housekeeping announcements, we will be recording this session and we'll make it available within a week. The webinar will ask about 25 questions. 25 minutes with time for questions at the end so so you can submit your questions to supportlearpointstrategy.com any that we don't get to at the end we'll follow up with via email. Now on our last installment, we dove into our spring cleaning ebook to erase clutter from your clearpoint account. I highly recommend checking out that recording on our blog, support center or Vimeo channel. Just search webinar now today we're talking about calculations and aggregations. These automations in clearpoint let you bypass entering data by hand so that you can quickly and reliably get the numbers you need. And not only does this save you that data entry time, but you also save time debating whether the numbers are right. That way you can get right to analyzing the meaning behind the numbers. So we'll show you how to create some commonly used calculations in clearpoint. Then we'll build on those calculations to some more advanced options. We'll dive into aggregations and we'll show you a new function of clearpoint 11 aggregateseriestatus so that's more than enough intro and we've got a lot to cover today. So let's get started. Per usual, we are flying the friendly skies here at Upward Air. Now you might as well call this Throwback Thursday because for our first calculation we're taking it way back to fourth grade math class with some simple division. I'm going to hop over to our Eastern Division scorecard and I'll click into my revenue measure. And here in the data table I have an actual series and a target series for our expected revenue. So maybe I want to see my actual revenue as a percentage of where I expect it to be. So I've set up a series here called percent of Target and I'm going to set up a calculation to divide the actual series by the target and view it as a percentage. So in order to edit my percent of Target series, all I need to do is double click on the series header and from here I'm going to navigate right over to the Calculation tab. This tab is going to Be your best friend today. You might even call it the calculation station. We've got everything here that we need to start building our calculation. And as we build it, we're going to have it appear here in this panel.

Clearpoint's calculation engine is really powerful. So you can pull data from any series in clearpoint into any other series in clearpoint. That's why we have these drop downs to specify where the data is going to come from.

Yep, good point. And we're going to start it off really simple here. So we're going to divide actual by target. So let's start with referencing the actual series right here in our revenue measure. So we've got the eastern division selected as our scorecard. We're choosing data from the revenue measure and for my series I'm going to pull in a value from my actual series. That last dropdown aggregation is the period whose data you're referencing. The option that pops up here by default is current period, meaning that whichever period the data is shown in, that's the period that's going to be referenced.

Yep. So November, for example, percentage of target will show the November actual revenue divided by the November target revenue.

Yep. Now, to pull this calculation, this value that we specified into our calculation, I'm going to click the insert button, which you could call the Harry Potter button if you're a Harry Potter fan.

I mean, we do sometimes refer to this page as a calculation wizard.

Yep. We'll see a token magically pop up in the calculation panel when we click Insert. So let's deconstruct this token quickly. What you see here first is a unique ID that helps clearpoint identify which measure the data is coming from. Then you can see the series name here in plain English and our aggregation, which is current period. All right onto the division. You can use your keyboard for this, but we've also got some handy operator buttons available to save you the trouble. So I'll click on my divide sign and it'll pop up right here in the calculation panel. Now this time we'll go back to our drop downs to insert the target series. So we're in the same scorecard, same revenue measure, and I'm going to swap out my series to reference the target. Again, we're using just the current period value, so I'll click insert. Now lastly here I'm going to want to use my operator multiplication symbol to multiply by 100, which I'm going to type in on my keyboard. That'll make sure that 0.43 turns into 43%.

And let's hope we're hitting a better percent of target than 43%.

Definitely. So I'll click save. And there you have it. Our percent of target series is grayed out, which tells you that the information is calculated. And we can see here automatically how close we got to hitting our target for each month.

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.

Right. So let's take a closer look. I've got another blank series here in my data table, which is labeled YTDactual. And in here, we want to display our total revenue up to this point in the year. So again, to edit the series, I'm going to double click on the series pattern and we'll hop back over to calculations. And in the drop down again, we're going to use the Eastern division revenue measurement and we'll use our series drop down to pull in the actual series. But this time we're going to make a change in the aggregation drop down. Wow, look at how many options we have here. We can do all manner of references, whether it's a specific period, a previous year, an average, or a sum, all with just a couple of clicks. And for this purpose, since I want to see my year to date revenue, I'm going to choose calendar year to date sum. Then I can click insert. And you can see in our token, rather than current period, we're referencing that calendar year to date sum for the actual series. And that's our whole calculation. I can click save. So here in the data table, our revenue builds up month after month to December, and then when we get to January, it starts over anew.

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 of more measures in our account and we can use any of them in any combination in all of our calculations.

Yeah, if you took all the possible calculations you could perform in just this account in clearpoint and laid them end to end, they would stretch to the moon and back.

It's true. We've done it. There are some clearpoint calculations floating around the space station up there.

Yeah, it's almost like a calculation constellation. All right, let's take a look at our net profit measure. Now, net profit is subtracted or is calculated by subtracting our expenses from our revenue.

So do we have to enter revenue and expenses again in the net profit measure to calculate that?

Heck, no, that would be a waste of time. And wasting time is not what clearpoint's about. We'll set up another calculation to do this for us. So let's scroll down and double click into the actual series and we'll go back to the calculation tab. And this time, even though we are looking at our net profit measure, I'm going to choose to reference information from the revenue measure so that I can subtract my expenses from that revenue measure. So I'll switch my measure over to revenue, grab the actual series again, current period value, and we'll click insert. Then we'll use our operator keys for subtraction, and I'm going to switch measures again so that I can subtract the actual value from my expenses. So we'll scroll down and choose expenses in the Eastern division. Again, we want our actual series. The value is going to be for the current period and I'll click insert. Now, this is where it might come in really handy to know what measure these IDs are referring to. If you look back at this calculation later, it's not very clear. So all I need to do is click test to see these in a little bit plainer English here I can see in my calculation that we've got the revenue measure from the Eastern Division scorecard, and then we can see actual and current period and that we're subtracting our expenses in the Eastern division from our revenue. So when I'm done with that test, I can click close and save. And now we can see our net profit without having had to enter a single value. Now 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.

For a city, this number might be population and various subsets of that population you needed to calculate crimes per 100,000 residents, voter turnout, unemployment rate, greenhouse gas emissions per capita, and the list goes on.

Right. And for our airline, the total number of flights is a big one. We use total flights to calculate percent of on time flights, maintenance cost per flight, and more. So how can we ensure that all of those measures are referring to the same total flight number? Well, we could spend time debating the numbers we used, or we could just add a reference measure to clearpoint. So I've actually got a separate scorecard set up in my account called Reference measures, and these are measures that I need in more than one place in my account. So we can see that total flights is one of them. And in our data table, we have a central number for the total flights in the Eastern, Central and Western division each month, as well as a sum of all three. So 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 what number is being used. All right, we've covered some basic operations, now let's dig into some more advanced calculations. Clearpoint lets you move beyond the basic operations we've covered and use certain SQL functions for absolute value exponents and more.

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.

That is true. So for our next calculation, I'm going to go back to my revenue measure and maybe we want to set up a calculation here where the end result is different based on various scenarios, a sort of if then statement. And looking at my year to date actual series, I can see that it populates even when there's no number in the actual series. That might be confusing or even panic inducing. When we look in December and we see that we only have $8,000 worth of revenue, it would be better to say that if there's a value in the actual series, then we'll display the year to date. Otherwise we'll leave it blank or null. So let's make that change to our year to date actual statement. So I'll double click into the series, go to the calculation tab, and I'll take out what we currently have here. Now, in SQL, an if then statement is called a case when calculation. And we've actually got a handy tool built into this window to help you set that up. So I can start my calculation by clicking into the functions dropdown and choosing case when. And we'll have two cases or scenarios here. In one case, the actual series is null, and in that case we want the year to date series to be null to. In the other, the actual series has a value. And in this case we're wanting to show the calendar year to date sum of the actual series. So let's set up that first case. We have case one entered already, and we want to show when the actual value which we use our drop down to pull in for the current period is null. So we can just type case when actual current period is null. Then we want to specify what happens in that case. So we'll type then into our calculation. And in this case we want the series to be null too. So the then is going to be null. We have our case when the actual current period value is null, then our year to date actual series will be null.

Also.

Now we want to define what will happen otherwise. So for this, we'll go back into our function dropdown and click else. Else is going to be our otherwise. So in our other scenario, when actual does have a value, we want to show the calendar year to date sum. So this time we'll use our dropdowns to choose the calendar year to date sum aggregation for our actual series and click Insert. Then lastly, we do need to end the calculation to show that there are no more options. So I can go back to my function dropdown one last time and insert end. All right, let's save it and see now. With our one calculation, we can now see that we only have year to date actual values when there's data in the actual series. Okay, so let's combine everything we've done so far into a super sleek calculation for our lost time incident rate. And I'm going to head over to that measure now in the Upward Airlines corporate scorecard. Great. So our lost time incident rate is the amount of lost time due to injury and illness each month.

So obviously we're trying to keep this number as low as possible, right?

Not only do we want to have records of our ltir each month, which we have here already, but we want to be able to determine the best case scenario for the end of the year. That is looking ahead, what would be the end of year average if there were no additional lost time for the rest of the year? Now, it's pretty hard to calculate that average when you don't have soothsayer abilities to see into the future and determine your data for future periods. But we can get around that by creating a new series where any null periods that we have here in the future are going to just display as zeros. That way we can incorporate all those periods into a best case average for the end of the year. So we'll start by double clicking into my future zeros series and we'll use another case one calculation here. So into our function drop down, we'll select case one, and our first case is going to be again when the actual series is null. So this time we have our Upward Airlines corporate scorecard up ltir measure, and we're going to pull in the actual series current period and specify that this is the case when it is null. Then instead of this series being null too, like our year to date actual, we're going to Want to display a zero, so I'll just type zero. Then for our other scenario going to insert else. And our other scenario is just going to be to display the existing value from the actual series. So we'll insert that again, use our function, drop down to end the calculation and click Save. All right. And here you can see our actual values with our zeros and future periods. And there's one more layer to this that will allow us to show the best case scenario for the end of year ltir monthly average. And we can't change our past data, but every month we're aiming towards zero. And of course, our future zero series does show what our data would look like if that were the case. So in our best case scenario series, we'll show the year to date average of our future zero series. So I'll go to calculations, and here in our ltir measure, I'll choose the future zero series. And this time it'll be a calendar year to date average instead of a sum. So once I've got those specified, I'll click insert and save my calculations. Awesome. So we can see here what our data would look like each period for the rest of the year if we had no additional lost time.

Now we're really interested in the best case scenario for the end of the year, which since we're looking at February of 2018, is going to be the December 2018 period from the best case scenario series.

Right. So what I'll do is to use that last series we created to showcase just the December value. Going to click into my last series here, end of year best case scenario. And for our calculation, we're going to reference that best case scenario for a specific period, with our specific period being the end of the year. So I can choose specific period. Here, I'll Specify December of 2018, click insert and then click States. Now, when we get to the end of 2018, we would want to switch that to 2019. But for now, in our data table, we can see what the end of year monthly average will look like if we're able to avoid any incidents for the rest of 2018. And if we wanted to, we could hide the intermediary series we created from the data table by double clicking.

And.

Checking the box to hide the series from data tables and summary reports. And you get a nice clean view of our best case scenario. 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. Well, that's what our support team is for.

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.

He's the calculation crustacean.

Wow, this is embarrassing. Did you actually make that?

And with that, it's time to shift the calculation conversation over to Neil to talk about aggregation.

Thanks, Kathryn, 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, and those are aggregations. Aggregate series are a powerful way to quickly add or average data across multiple departments or divisions. Think of it as a sum all function of clearpoint. For example, if I click into the revenue measure of our Upward Airlines corporate scorecard, I can see that there is a revenue measure for each one of our divisions, Western, Eastern and Central. If I click into the Eastern division, I can see a data table with an actual and a target series. Our account is actually 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 the divisional revenue measures in the corporate scorecard. So if I go back to my corporate revenue measure, go to the actual series, and go into the calculation tab, we can see that I've manually entered the actual values into my calculation. But there's an even easier way to perform this roll up, and that's if I use the aggregate series function. So 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. So from the series calculation type, I'm going to select the second option of aggregate series. What this allows me to do is pull in measures from throughout the system and perform map operations on them.

Right. So with aggregations, we're specifying criteria for multiple series here rather than the individual series themselves.

Exactly. So 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 the Upward Airlines corporate scorecard. And you'll see how the number on the right shifted to three. And this tells me that there are three child scorecards that fit this criteria. Next, I need to clarify which measures I want to pull from, since I know each divisional scorecard has a revenue measure from the select Measures pane, I'm going to select measure name equals. However, you'll see that there are three other options and those are all measures child measures of the current measure you are on in case you have a measure set up in a parent child format or measure name contains if you don't have a precise naming convention across all your divisions. So if I select measure name equals, I can designate right in this line that I want to look at all of the revenue measures. And you'll see again that the number shifted to three, indicating that there are three measures in the children's scorecards that are titled Revenue. Next, we're going to go to the series name match type and we're going to say series name equals again, because we know that all of the child measures have an actual series, right?

So because we chose series name equals, they do all have to have the exact same name in order to be incremental.

Yep, exactly. So I'm going to type in actual. Finally, you decide whether you want to sum, count, average, or take the standard deviation. Since we're finding total revenue, we're going to want to sum the values from each series. Additionally, I can choose to ignore the blank values, or I can treat them, or I can treat the blank values as zeros. So unlike in a standard calculation, blank values won't prevent our sum from appearing.

Neal is referring to the fact that a standard calculation will only appear if all values referenced contain data. And that 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.

That's a good point, but for now, I'm just going to 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. Now we're going to move along and talk about aggregations using averages, which is a second capability of the aggregate series function. For this example, we're going to still be in the Upward Airlines corporate scorecard. I'm going to move on to a new measure of expenses. I can see that there's already an actual series calculating the sum of expenses from the three divisions. However, say I'm in the corporate budgeting office and I also want to calculate the average expenses across each division. What I'm going to do is double click into the data table, create a new series, and I'm going to call it the divisional average. If we navigate to the calculation tab, we can follow nearly all the steps from the previous sum example. However, for this Example, I want to pull in measures of children in Upward Airlines. Select measure name equals and again, this one's expenses series name is going to equal actual again. But for the aggregate type, we're going to want to select average. This is where treating blank values as zeros or ignoring the blank values comes into play. With a standard calculation, I'd have to specify that I'm dividing the sum by three to get the average because because there are three divisions. So if there were blank values at the division level, it would throw the averages off. But with aggregations, I can choose to ignore blank values and clearpoint will adjust. So if I only have two divisions entered in data, the sum will be divided by two. So now all I have to do is hit save and on the right you'll see our new series that is across our city offices what the average expenses are across the entire 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're 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 Kathryn outlined. So this was a good introduction to the capabilities of the aggregate series. Now let me walk you through a new calculation type in clearpoint called the 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 a sum all function of all your element statuses. We've seen this be particularly useful for project management to see how you're progressing on initiatives and action items. Let me show you exactly how this feature works. So we actually have a measure created already called Initiative Status Snapshot. 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. So our measure, as you can see by the data table, is tracked monthly, but maybe yours will be quarterly depending on how your organization is tracking projects. You can see in my data table that I also have five 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'm going to walk you through how to do so with the completed series. So if I double click on the header of my completed series and click into the calculation tab, we could use from this drop down the last option of Aggregate Status. For this example, I'm going to only want to look at the Upward Airlines corporate scorecards. So I'm going to designate that we're looking at a specific scorecard and from the next drop down choose Upward Airlines Corporate. Next, I know that I want to look at all of my initiatives, so I will choose initiative as my element type and I'll designate this as all elements. Finally, since this is a completed measure series, I'm going to clarify that the status should be completed. Then all I have to do is hit save and you can see that we have five projects that are completed in February of 2018. Now I'm going to repeat these steps in a similar manner for the total series. The only difference is, you will note, is going to be changing the status that we want to count. It's that simple. So I double click in go to the calculation tab, select Aggregate Status. Again, we're looking at a specific scorecard of Upward Airlines. We're looking at initiatives. But now in the status Indicators field we're going to pick completed above Target Caution and below Plan, which is the four series for statuses that are possible in our current account. And if we hit save, we can scroll down and see that currently we have 23 initiatives with statuses in February of 2018. After I do this, I have a nice easy to comprehend data table.

Yeah, this is really great. And the cool thing about aggregate statuses is that once you've created those aggregate numbers, you can use them just like any other series and standard calculations. So we could divide one of our individual status series by the total, for example, to find the percentage of initiatives that are on target this month.

Exactly. And there is one chart that we have found particularly useful when looking at projects, and that is a pie chart. You might have only thought of your charts as being useful for measures 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, so you have 100 initiatives and they all have their own status. This pie chart will show you how you're doing collectively in the project at a particular point in time. So I'm going to do is double click into this blank chart here and my chart type is going to be designated as a pie chart. I'm also going to remove the legend because each series name will show up next to its respective slice on the pie chart. Second, I'm going to go to the Chart Periods tab and for periods to display, I'm going to select the current period. 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 a quarter. Finally, I'm going to go into the chart series tab and clarify which series I want mapped out on my chart. I'm going to also clarify color, so it's an easy to read format. So for green status I'm going to pick green, yellow, I'm going to pick yellow, and so on. Now all I have to do is refresh my preview and I'll see how my chart looks alright and it looks great and all I have to do now is hit save and I'll have a nice chart appear on my detail page. So this whole example was done for initiatives that 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 status measure already built out as you can see here with the chart similar to the one we just built. Now that we have these two status snapshot measures built out, we have a solid overview of how our projects are currently doing. To take it one step further, we have a cool way to show how your project's doing on a high level view and that is to create a project dashboard. Again, for the sake of time, we've already built this one out, but I'm going to walk you through it right now. On the right you'll see the two charts that we just built. Here we are referencing both of the aggregate status charts to see a high level snapshot of our progress. If I go to the bottom we have a Project Dashboard reference report and it's just an initials report and it shows who owns each project, the end date, any linked milestones to complete each one. Of course you can customize the columns in your account to show any important information that you want to see. Finally, on the left hand side we have this Action items report and this one 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 that they are responsible for overall. This assists in the progress and management of your strategic projects and tasks.

This is awesome. Who knew measures could be so useful for project management in clearpoint? And if you have any questions about how to create this view, we know we walked through it quickly. So if you want to set up something similar, feel free to reach out and we'll help you out. So we're reaching the end of our time here today. But I do want to answer one important question that's come in and that is how can I do a fiscal year to date calculation? How will clearpoint know what my fiscal year is? So clearly we have an observant attendee who saw in our aggregation drop down in the Calculation tab that you do have the ability to do a fiscal year to date, sum count or average. However, there is one intermediary step there in order to take advantage of those aggregations. This will be something that administrators can set up under System Settings, Reporting periods, and then the third toggle in the blue toggle in the top right here we can see the fiscal years that we've set up in Clearpoint and they are in each year from July 1 to June 30, but we can see that they end in June of 2017. So that's when our fiscal year today calculations would stop working. And in order to correct that, all we need to do is click the plus icon and add an additional reporting period. And it's nice, a nice little shortcut here that clearpoint is actually going to take the dates of your existing fiscal years and just add on to that. So we can see that our fiscal year is going to start July 1st and ends June 30th of 2018, and that's going to allow our fiscal year to date calculations to populate accurately. Great. So we've reached the end of our time here 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.