Calculating on-hold minutes
1. Calculating on-hold minutes
Let's write a row-level calculation to calculate how long each customer was on hold during their call in minutes. To add a new column, we can click the plus sign next to add column, or I prefer to click the downward-facing arrow next to where I want the new column to be added, then select add column. Double-clicking the column header lets me type in a new name for the column I'm creating - I'll type Hold Duration. As I move to the formula editor bar and begin to type a function, Sigma prompts me with columns and operators to complete the syntax. For this example, I'll need a function called datediff to find the elapsed time between when a customer's call was received and entered the hold queue, and when their call with an agent began. As I type DateDiff, Sigma prompts me with the function syntax. Clicking the syntax will insert it into the formula editor, and I can now see a description of the syntax parameters. This function requires 3 parameters - unit, start, and end. For the unit, I'll type minute to be sure that we calculate the elapsed time in minutes. Note how I include quotations. To reference a column, I can either type in the name, like Call Start. Or I can click on the column in my table element. Since we're calculating the hold time, I'll click on Hold End Time. Hitting Enter applies the formula, and results appear in your new column. Now I can check my calculation. For this row, the call was initially received at 3:44, and on hold until the agent answered at 3:52, 8 minutes later; so, our calculated value is accurate! The Formula Bar also helps catch and fix mistakes. Here, I'm calculating how long someone has been a customer. I get a warning icon and an orange highlight, with a red squiggly line showing where the issue is. Hovering over the red line or caution symbol will explain what Sigma expects. And updating my syntax to the proper today function and hitting Enter resolves the error. Sigma will even help you write your syntax more efficiently! If you see a blue squiggly line under your syntax, thats Sigma suggesting a better way to write the function. In this example, we've used a concat function to combine the customer's first and last names into a single cell. It works, but hovering over the blue line in the formula editor shows me that there's a simpler way. We'll click Apply formula to update to the convention using ampersand symbols instead. When creating calculations, it's also worth considering how the results will appear. For example, adding a comma can make values like Days as Customer much easier to read. To change the format, just use the drop-down in the column header to choose a display format. You'll also find formatting options in the toolbar at the top of the workbook page. In this next exercise, you'll create your own row-level calculation and update the format to make the result easier to understand.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.