USE FUNCTIONS TO CREATE CUSTOM CALCULATIONS
Perform advanced calculations within teamdeck
There are over 30 built-in functions in the reports section that you can use to calculate the metrics that matter to you. Some of them are more straightforward than others, so here’s a practical overview of all of them to show you how they can be useful in your reports.
How can I use these functions?
Use these functions to create the so called “custom calculations”. You can set them up in the reports. Create your report and add a chart of your choosing. Now, click on metrics in the upper-right corner of your chart/table. Here you can find a list of predefined values and calculations. If you can’t find what you’re looking for, it’s time to create a custom calculation. Click add custom and add custom calculation field.
Here, you will see a list of different functions that will help you calculate your metric. In this article, we will go through each of them.
Instruction on how to set up custom calculations:
Learn more about customizing your report here.
Note: your custom calculation might include custom values like your employees’ hourly rate. We have predefined some important values but if there’s anything else you need, you can add it by clicking add custom values column. Remember that these values have to be filled out manually so you need a table to write them in. You can later use these values in any chart or calculation.
Built-in functions:
Arithmetic functions:
Use these functions to calculate your numeric data with simple mathematical operations. Want to monitor your employees’ absenteeism rate or count freelancers’ payroll? You’ll find them useful.
abs(x) - Calculate the absolute (the non-negative) value of a number.
add(x, y) - Add two or more values. You can also do it by using an arithmetic formula: x+y.
ceil(x) - Round a value towards plus infinity. This function returns 4 when the value is 3.2 or 3.8, returns -4 when the value is -4.7 or -4.2. For other functions that round your values check fix(x) and floor(x).
cube(x) - Calculate the cube of a value (x*x*x).
divide(x, y) - Divide two values: x / y. You can also do it by using an arithmetic formula: x/y.
fix(x) - Round a value towards zero. This function returns 3 when the value is 3.2 or 3.8, returns to -4 when the value is 4.7 or 4.2. For other functions that round your values check ceil(x) and floor(x).
floor(x) - Round a value towards minus infinity. This function returns 3 when the value is 3.2 or 3.8, returns -5 when the value is -4.2 or -4.7. For other functions that round your values check ceil(x) and fix(x).
mod(x, y) - Calculate the modulus, the remainder of dividing one number by another (remainder of x/y). For example, mod(8,3) would return 2 and mod(9,3) = 0.
multiply(x, y) - Multiply the values. You can also do it by using an arithmetic formula: x*y.
pow(x, y) - Calculate the power of x to y (xy).
round(x [, n]) - Round a value towards the nearest integer. Returns 3 when the value is 3.2, returns 4 when the value is 3.8. Returns -4 when the value is -4.2, returns -5 when the value is -4.7.
square(x) - Calculate the square of a value, x * x.
subtract(x, y) - Subtract two values. You can also do it by using an arithmetic formula: x - y.
Relational functions:
Use these functions to investigate relations between your data. Say you want to take a closer look at the relation between the estimated hours and the actual time spent on given projects. Ideally, these values would be close to equal. These functions allow you to spot patterns and identify alarming cases in your data.
compare(x,y) - Compare two values. This function returns 1 when x > y, -1 when x < y, and 0 when x == y. Pick whole number as the output data format when using this function.
equal(x,y) - Check whether two values are equal. This function returns 1 when the values are equal and 0 when they’re not.
larger(x,y) - Check whether the value x is larger than y. This function returns 1 when x is larger than y and 0 when it isn’t.
largerEq(x,y) - Check whether value x is larger or equal to y. This function returns 1 when x is larger or equal to y. Otherwise, it returns 0.
smaller(x,y) - Check whether value x is smaller than y. This function returns 1 when x is smaller than y and 0 when it isn’t.
smallerEq(x,y) - Check whether value x is smaller or equal to y. This function returns 1 when x is smaller or equal to y. Otherwise, it returns 0.
Statistics functions
Use these functions to get statistical information about your data - identify the highest and lowest values, calculate the median or figure out what’s the total sum of the chosen items.
Remember to select an appropriate data format for this calculation, as it should match the format of the items you’re analyzing. If you’re looking for the highest value among the time-based metrics, you need to pick time as your data format.
max(a,b,c, …) - Identify the maximum value of a list. This function will return the highest value from the provided items.
average([a,b,c, ...]) - Calculate the mean of a list with values.
median(a,b,c, …) - Calculate the median of a list with values. The median is a middle score for a set of data arranged in an ascending mode. It’s a good method of assessing the central tendency of your dataset because it’s not particularly affected by the outliers (e.g. items with extremely low or high values).
min(a,b,c, …) - Identify the minimum value of a list. This function will return the lowest value from the provided items.
sum(a,b,c, …) - Calculate the sum of different values.
Logical functions
not(x) - Logical not. It returns 1 when input is 0/empty value, otherwise it returns 0.
or(x,y) - Logical or. It returns 1 when at least one of the inputs is not empty. When both inputs are empty it returns 0.
xor(x,y) - Logical xor. It returns 1 when only one of the inputs is not empty. When both inputs are empty or not empty it returns 0.
Get and nested functions
Get and nested functions allow you to analyze different groups of data (people, projects, tags, weeks) together with metrics (timesheets: time or vacation days). Use get and nested along other functions, like sum or average, to get the calculation you need.
What’s the difference between get and nested?
get(group, metric) - Will perform your calculation on all instances of the chosen group in your table.
nested(group, metric) - Will calculate the value of the instances that are nested below the elements of a given level in your table.
Say you want to analyze the average working time recorded by your team in a given month. You have generated a table which is grouped as follows:
- Projects
- People
- Weeks
You introduce a custom calculation, let’s call it “Average recorded time”:
average(get(Group: People, Timesheets: Time))
This formula will return the average time based on the timesheets of all people in all projects. This could be useful, of course, but you might want to drill down your data and calculate this value separately for all of your projects.
Since people are grouped below projects in your table, you can use nested:
average(nested(Group: People, Timesheets: Time))
This time, you will see a different average working time for each project.
Other functions
total(metric) - This function allows you to display a total value (sum) of another metric (e.g. timesheets: time). You can use both the metrics that are already displayed in your table or the ones that aren’t displayed as a separate column.
count(group) - This function allows you to count all of the elements that belong to a chosen group (e.g. people or projects). Say you want to count how many people have been assigned to each project:
count(nested(group: People))
Note that your table has to be grouped in a way that People are nested under the Projects.
countif(group, metric) - This function allows you to count all of the elements that belong to a given group and meet the chosen conditions. The previous function (count) made you see how many people were assigned to the projects. Say you want to check how many people have actually logged their time:
countif(nested(group: People), Timesheets:Time > 0)
Calculations like that allow you to spot which projects need extra manpower and which people are underutilized.
if(expression, valueIfTrue, valueIfFalse) - Use this function to set up a field that displays a value of your choice based on a given condition.
if(Timesheets: Time > 480, overtime, ok)
This function would display “overtime” next to a person who have logged more than 8 hours in their timesheet. Otherwise, it would display “ok”.
Note that valueIfTrue and valueIfFalse could be displayed in any data format (number, text, currency etc.).
sumif(array, ifStatement, expression) - This function allows you to sum only the instances that meet a chosen condition. Say you want to sum the hours tracked for each project but you’re only interested in people who have spent more than 5 hours working on a given project. You need to decide which group you want to analyze (e.g. people or projects), what the condition is and, finally, what should be summed should your condition be met:
sumif(nested(Group:People), Timesheets: Time > 300, Timesheets:Time)
THAT’S A WRAP!
Do you have any questions about teamdeck’s reports and custom calculations? Or perhaps there’s another function you’d like to see on top of the ones you can already use? Let us know!