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 helpful.
abs(x) - Calculate a number's absolute (the non-negative) value.
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 and 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 and 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 and 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 using an arithmetic formula: x - y.
Relational functions:
Use these functions to investigate the relationship between your data. Say you want to look closer at the relation between the estimated hours and the 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 the 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 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 must 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(array, [ignoreEmpty]) - Calculate the mean of a list with values. Optional ignoreEmpty argument if set to true omits the 0 values provided in a list of values.
median(a,b,c, …) - Calculate the median of a list with values. The median is a middle score for a data set 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 the 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.
String functions
indexOf(metric, phrase) - Returns the first occurrence of the specified substring
Get, nested, and ancestor 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 calculate all instances of the chosen group in your table.
nested(group, metric) - This will calculate the value of the cases nested below the elements of a given level in your table.
ancestor(group, metric) - Returns the value of a metric on an ancestor group relative to the group that is analyzed.
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 the metrics already displayed in your table and those not displayed as a separate column.
count(group) - This function allows you to count all 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: for the example above your table must be grouped so people are nested under a different group e.g. Projects.
countif(group, metric) - This function allows you to count all elements that belong to a given group and meet the chosen conditions. The previous function (count) showed how many people were assigned to the projects. Say you want to check how many people have 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 has logged more than 8 hours in their timesheet. Otherwise, it would display “ok.”
Note that valueIfTrue and valueIfFalse can 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 calculate the hours tracked for each project but are only interested in people who have spent over 5 hours working on a project. It would help if you decided 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)name() - This is a special function that takes the name (first column in a table) of a given row. It is useful in combination with the if function to execute a different logic for a specific row:
if(name()=="Developer", booking tags: Development, bookings: time) isEmpty(metric) - Checks if a metric has any value. Especially a handful to check a Custom Value metric, but can also be used to check if a custom field is provided.
isGroup(group) - Checks if the group of the row the formula is executed on matches the group provided in an argument:
if(isGroup(Group: People), Custom Metric: Person Cost, sum(nested(Group: People, Custom Metric: Person Cost)) The formula executes a different logic for people rows and all other groups e.g. Projects providing the sum of nested people values for a person cost on any other group than people which would be the total cost of all the people for a project.
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!