Constructing Calculations in ClearPoint
Webinar 71 in the "I Didn't Know ClearPoint Could Do That!?!" Webinar Series
- Intro (0:00)
- Agenda (1:30)
- Why are Calculations in ClearPoint Useful? (2:50)
- Percent Change as a basic example (3:29)
- YTD, QTD, FYTD Calculations (8:11)
- Case When on FYTD Calculation (11:59)
- Aggregations (15:30)
- Reference Calculations (18:42)
- Poll & Questions (21:25)
Henry: Hello everyone, and welcome to the “I didn’t know ClearPoint could do that!?!” webinar Series
Today, we’ll be talking about how you can construct helpful calculations in ClearPoint. We have several best practices that we are excited to share with you that will hopefully make your ClearPoint experience better and more efficient!
But before we get started, here are your smiling hosts, I’m Henry!
DeAndre: And I’m DeAndre!
Henry: As a few housekeeping announcements:
We will be recording this session and will make it available within a week.
The webinar will last about 25 minutes with time for questions at the end.
So you can submit your questions to our Q&A chat box!! This is located in the toolbar at the bottom of the Zoom window.
As always, any questions that we don’t get to, we’ll follow up by email!
Finally, the Comments box is meant for you to add any additional insights or thoughts that you may have as we go through the webinar.
Now, on our last installment, we hosted a webinar on how to create templates to save you time, so if you want to learn some awesome tips and tricks on topics such as setting default page layouts, setting summary report templates, and more, be sure to check out that recording and transcript on our website or Vimeo channel! Now let’s get into the topic of today and learn about constructing calculations in ClearPoint.
DeAndre: First, let’s talk through an agenda of topics for today.
We will walk through why calculations in ClearPoint will be useful for you. Then we will dive into ClearPoint and go over a percent change calculation to show how to create basic addition, subtraction, multiplication, and division calculations work. We then will review “To Date” calculations, meaning the calculations that allow you to do Quarter to Date, Calendar Year to Date, or Fiscal Year to Date calculations within a single Measure.
Next, we will discuss the “Case When” function, which essentially allows you to use “If…then…” statements in your calculation. Following that, we will go over an example of how to use Aggregations and finally, we will review reference calculations.
Henry: There is clearly a lot to go over today, and we will only be going over an example or two of how to utilize each of these features within calculations. However, there is definitely a lot more that you can do than what we will show! I would recommend taking some time at some point after this webinar to explore calculation options in your account and be sure to reach out to your account manager if you have any questions on what calculations you could use!
Why are Calculations in ClearPoint Useful? (2:50)
Henry: Before we dive into ClearPoint, let’s discuss why utilizing calculations may be helpful for you.
Calculations let you bypass entering data manually so that you can quickly and reliably get the numbers you need for analysis. Furthermore, it lets ClearPoint do the calculations for you so that you can be confident those calculations are right and that there is no human error. That way, you can get right to analyzing the meaning behind the numbers as calculations are done in real time. That way, your decision-making and data analysis can be more efficient and reliable day in and day out.
Percent Change (3:29)
DeAndre: That sounds great! Let’s dive into ClearPoint and get started. We can begin by going over a percent change calculation so that we can see a basic calculation example.
Henry: That’s great! There is a set of data that I currently have here in this ticket price differential Measure, and I need to understand the variation between sets! Can you show me an example of how to leverage the percent change calculation?
DeAndre: Absolutely! Let’s dive right in. Here we must create the data for the calculation. We can do this by editing this Measure and navigating to the Series tab. By clicking the plus icon, we can add another Series to this Measure. I named this Percent Change along with changing the data type to percentage. Next, we’d have to add the calculation by clicking on the calculation tab and select Calculated Series type.
As a reminder, to calculate percent change you want to take the new value minus the old value and divide by the old value and multiple by 100. In ClearPoint terms, that means taking the Current Period minus the Previous Period, dividing by the Previous Period and multiplying by 100. Here, we will be setting criteria.
Start by clicking the Scorecards dropdown and selecting Upward Airlines. Next, select Measure from the element drop down. For Measure we are looking at ticket price differential. Under the Series drop down, we will select the Actual into the Series Name field. Finally for Aggregation type, we will input Current Period. Then insert this token, next we will subtract the Previous Period within parentheses. Next, we will divide this difference by the Previous Period again closing this into parentheses. Lastly, we will multiply by 100.
Now that we’ve inserted our calculation, we can test to see the calculation before we implement it by clicking test. Once that’s done, click save and save now we can see the percent change calculation presented on our data set that represents the change from cell to cell. How does this calculation look?
Henry: Woah, this is super helpful! I can see how once you have the calculation set, it makes it easier to analyze my data more quickly and accurately. Quick question – negative percentages are not attractive; is there any way I can keep my percentage positive?
DeAndre: Yes, we can! Clearpoint can add functions to your calculations to accommodate for these situations. One of these many functions is the Absolute value ABS(). Applying a function is simple and straight forwards. Here I can show you! Let’s go back to our calculation by double clicking the Series data we just applied the calculation to. This pop-up will look familiar right?
Henry: Woah yeah, it’s the same field when we clicked the plus icon on the detail page. That’s a cool short cut to know.
DeAndre: Yes that’s correct, clicking the calculation tab on the left you will see our previous percentage change calculation. Click function and a drop down will appear allowing you to choose ABS(). Insert this into the beginning of the calculation set and make sure the calculation is closed at the beginning and end with (). Remember you can always test the calculation before finalizing. Then you can hit Save. Now you will see all percentages will be represented positively.
“To Date” Calculations (8:11)
Henry: That’s great. Now that we have gone over how you can do basic calculations, I would love to show you how to do cool “To Date” calculations for a single Measure as well!
DeAndre: For sure! I actually noticed a Quarter to Date calculation for our Revenue Measure here. This is definitely super helpful when trying to see how we are performing each quarter. Can we walk through how to set a Quarter to Date calculation? I would love to see how Year to Date calculations work too!
Henry: Absolutely! As an example, let’s walk through how to add in a new Series that shows off a Fiscal Year to Date Sum. As we are setting up this Series, we will be able to see how you could easily set up this Quarter to Date Sum along with many other “to Date” calculations!
As an initial disclaimer before I build this calculation, this Webinar account is structured to have its Fiscal Year start on July 1st of each year and end on June 30th of the following year. All ClearPoint accounts will be defaulted to this Fiscal Year structure, but if you operate on a different Fiscal Year, please reach out to your account manager and they can quickly change the Fiscal Year structure for your account!
DeAndre: Great – that makes sense! Let’s dive into how to create this calculation.
Henry: Sure thing! To add a new Series, I am just going to double click this Measure Data Table pod header, and then click the green button to “Add Series.”
For the Series Name, we can abbreviate this “FYTD” for Fiscal Year to Date. We will leave the data type as Currency since we are tracking Revenue. We can click into the Calculation tab now. We will be prompted to save this new Series, so we will go on and do so.
For the Series Calculation Type, we will make this a Calculated Series. From here, we will leave the Scorecard as Upward Airlines, Element as Measure, and Measure as our Revenue Measure. For the Series dropdown, we will select the Actual Series as that is the Series that we want to calculate Fiscal Year to Date for.
Under the Aggregation dropdown, you can see we have quite a few “to Date” options to choose from! We can construct Calendar Year to Date, Quarter to Date, Fiscal Year to Date, and even more customized options from here. Notice that you have the option to calculate “to Date” Averages as well if that is something you are interested in.
For this example, we are going to be calculating a Fiscal Year to Date Sum, so we will select that option from the drop down. We will then click “Insert” to input that calculation. We can then click Save.
DeAndre: Wow, this is awesome! I can see for each Fiscal Year that there is a cumulative sum being calculated that is then reset at the start of the next Fiscal Year.
I noticed that for this current Fiscal Year that we only have Revenue data so far through December 2021. The Fiscal Year to Date Sum is then repeating the same value for the months of January 2022 through June 2022, which makes sense given there is no data input yet for those months. However, I was wondering if there is a way to only have the Fiscal Year to Date Sum appear only through the months that we have data?
“Case When” Function (11:59)
Henry: Yep, we can do that, and that is a nice segway into the next topic I wanted to talk about which is the “Case When” function. Have you heard of that function before?
DeAndre: Yes, I have, but can you give me a refresher on how it works?
Henry: Of course! Broadly speaking, the “Case When” function works like an “If, then” statement. In this example, we are going to set up a “Case When” function that essentially says, “If there is no data input for a month, then do NOT calculate the Fiscal Year to Date Sum, but if there IS data input for the month, then DO calculate the Fiscal Year to Date Sum.”
DeAndre: So to summarize, we are going to set up a “Case When” function that will calculate the Fiscal Year to Date Sum through December 2021 because we have monthly data through that month. Then, the Fiscal Year to Date Sum will no longer appear for January 2022 and onward because we have no Actual data input for those months?
Henry: That’s right! Let’s dive into how to do this.
First, we can double click on the FYTD header to edit the Series, and then we can navigate to the Calculation tab again. Let’s begin by deleting out the current calculation so that we can start from scratch.
We can now start the first part of the calculation, which is going to express that if there is no data input into the Actual Series for the period, then this Series should not calculate. We will begin this first part by clicking into the Function drop down and selecting “Case When.” We will then input the reference token for the Actual Series for the Current Period. Following this token, we will type out “IS NULL THEN NULL.”
DeAndre: Oh, I get it! This first part says if the data in the Actual Series is Null, meaning there is nothing input for the period, then the calculation should be Null for that period.
Henry: Exactly, great job catching on quickly! Now we just need to put the second part of the calculation, which will essentially say that if there IS data input for the period into the Actual Series, then this Series should calculate a Fiscal Year to Date Sum.
We can start this part of the calculation by clicking the Function dropdown and selecting “Else,” which just means that if no criteria is met in the first part of this calculation, move onto the next part. We then will follow the same procedure as outlined previously and input the appropriate Fiscal Year to Date token. We will finish off the calculation by clicking the Function drop down and selecting “End,” which just lets the calculation know that the “Case When” function ends there. We can now click Save.
DeAndre: Wow! The calculation looks great now. It only shows the Fiscal Year to Date up to where we have data input.
Henry: “Case When” definitely can be a helpful feature to utilize! Now that we have walked through these “to Date” functions that provide the ability to Sum or Average values within singular Measures, I was wondering if you could walk me through easy ways to aggregate across multiple Measures? I know that there must be easier and more efficient ways to do aggregate Sums, Averages, and more than just individually choosing each Series I want in each calculation.
DeAndre: Absolutely! Let’s run through an example of aggregating an average of a metric we are tracking in both the Eastern and Western Division scorecards. As we will see through this example, setting aggregate calculations through children scorecards is really easy and helpful. In this example, we will be looking to do an aggregate average of the Measure % on time departures. We are currently tracking % on time departures for both the Eastern and Western divisions individually, but we do not have any aggregate calculation set up currently.
To start, we will navigate to the % on time departures Measure that is at the top-level Upward Airlines scorecard. We will double click the Series header to edit the Actual Series. We can navigate to the Calculation tab, click the drop down for Series Calculation Type, and this time select Aggregation Series instead of Calculated Series.
From here, we will be setting criteria for the Measures and Series that we want to pull from. We can click the Scorecards dropdown, and you will notice that we can choose a few different options, but for this example we are just going to be selecting Elements in children of “Upward Airlines.” We will then select Measures as the element type and then select Measure name equals under the Select Measures drop down. In this case, we want to pull from all Measures named exactly “% on time departures.”
Under the Series drop down, we will select Series name equals and then input “Actual” into the Series Name field. Finally for Aggregation type, we will select Average and leave the final field to treat blank values as zeroes. Then click Save.
Henry: This is great! I can see from this example that if we had a lot of children scorecards that this would be far easier than trying to individually reference each Series into a Calculated Series. Not only that, I saw through each drop down there were a good number of other customizable options for Aggregations, such as selecting specific scorecards, utilizing tags, and also various Aggregation types.
Reference Calculations (18:42)
Henry: Hm, going through this Aggregation example has me wondering another question. For this example, where I am tracking the same metric across different departments or divisions, I wonder if there is a way to have some benchmarking instead of going back and forth from different Measure to different Measure?
DeAndre: This is another feature that’s going to make your job easier. I’ll show you! We can set up what are called reference calculations. Let’s get started.
First, we must find the measurement we want to add a reference to. As you can see here, I am in the Eastern Division Scorecard under Upward Airlines. More specifically, we are in the % of departures on time in the Eastern Division.
This department wants to reference how they are doing compared to the Western division. We can do this by double clicking the Measure data and add a new Series by clicking on “add Series”. Here we can set up what will be our reference data. Since we are referencing the Western Division, I will name this Series “Reference to Western Division.” Next I will make sure the data type matches the original, which for this case is Percentage.
Next, I will navigate to the Calculation tab. Here we can set up our calculation by clicking the drop down and going to the Calculated Series type. Below the calculation field, we must let Clearpoint know what Series we want to insert. We start by selecting the scorecard which in this example is “Western Division”, next we have to classify what element we are looking at which is a “Measure”. Which Measure will we be pulling specifically? The “% on time departure”. Next the actual Series is where the exact data lives so we select that, and we want to compare Current Period to Current Period so we will aggregate on Current Periods.
Once complete, we insert the data and select the save button. Looking back at our data set, we can see the “Reference to Western Division” represented and can now compare between the two divisions. Isn’t that cool?
Henry: That’s perfect! Now I don’t have to waste time shuffling back from scorecard to scorecard to compare different sets of data!