This article provides some simple formula examples to begin exploring the **calculated field **functionality. They are:

- Simple Arithmetic;
- Ratios;
- Date Calculations;
- Update / Creation History;
- Look-Ups;
- Aggregates;
- Users;
- Smart Tags.

**Simple Arithmetic**

When doing simple arithmetic the two most important things to keep in mind is to ensure that you are handling NULL values correctly and converting to the correct types.

**Subtract currency fields:**CONVERT(money,ISNULL(C01,0)) - CONVERT(money,ISNULL(C02,0))**Add decimal fields:**CONVERT(decimal(17,2),ISNULL(P01,0)) + CONVERT(decimal(17,2),ISNULL(P02,0)

**Ratios**

Remember to check for division by 0 errors when calculating ratios.

**Simple ratio based on currency fields:**CASE WHEN ISNULL(C02,0) = CONVERT(money,0) THEN 0 ELSE CONVERT(money,ISNULL(C04,0)) / CONVERT(money,ISNULL(C02,0)) * 100 END**Cost variance %:**CASE WHEN ISNULL(C02,0) = CONVERT(money,0) THEN 0 ELSE (CONVERT(money,ISNULL(C02,0)) - CONVERT(money,ISNULL(C04,0))) / CONVERT(money,ISNULL(C02,0)) * 100 END

**Date Calculations**

**Last updated:**Created**Number of days between the date on which a record was last updated and today:**DATEDIFF(day,Created, GetDate());**Task lag (difference in days between planned and actual start date):**ISNULL(DATEDIFF(d,TaskFrom, ActualTaskFrom),0)**Days overdue (difference in days between issue follow-up date and current date):**-(CASE WHEN I01 < GETDATE() AND IssueStatus NOT IN (466) THEN DATEDIFF(d,I01,GETDATE()) ELSE 0 END)

**Update / Creation History**

**Date on which project was created:**(SELECT MIN(Created) FROM tblProjects s WHERE s.CoalescedMasterKey = tblProjects.CoalescedMasterKey)**Captured by (the person who initially added a project):**(SELECT u.EmployeeKey FROM tblUsers u WHERE u.UserKey = (SELECT TOP 1 UserKey FROM tblProjects s WHERE s.CoalescedMasterKey = tblProjects.CoalescedMasterKey ORDER BY s.Created))**Updated by (person who last updated the record):**(SELECT TOP 1 EmployeeKey FROM tblUsers u WHERE u.UserKey = tblProjects.UserKey)

**Look-Ups**

**Look-up the project manager:**(SELECT Manager FROM tblProjects s WHERE s.ProjectKey = IssueProjectKey)**Look-up based on a dependency:**(SELECT TOP 1 ParentKey FROM tblMappingItem WHERE MappingKey = 33 AND ChildKey = C01)**Lookup value from a custom entity:**(SELECT X05 FROM tblCustomEntity1 WHERE CE1Key = {KC01} AND MasterKey IS NULL)

**Aggregates**

**Total budget for a project:**CONVERT(money,ISNULL((SELECT SUM(c.CostBudget) FROM tblCost c WHERE c.CostProjectKey = tblProjects.ProjectKey AND c.MasterKey IS NULL),0))

**Users**

**Check whether or not an employee is a user:**(SELECT CASE WHEN EXISTS (SELECT 1 FROM tblUsers WHERE tblEmployees.EmployeeKey = tblUsers.EmployeeKey AND tblUsers.Active = -1) THEN -1 ELSE 0 END)

**Smart Tags**

You can refer to other calculated fields in your formula by placing the field's name in **curly braces**.

**Calculate cost variance as a percentage of the budget:**CASE WHEN CostBudget = 0 THEN 0 ELSE**{CostVariance}**/ CostBudget END**Calculate custom list value with case statement:**CASE WHEN IssueStatus IN (242) THEN 573 WHEN {IC05} < 0 THEN 570 WHEN {IC05} BETWEEN 0 AND 7 THEN 572 ELSE 571 END**Calculate the total cost variance on a project:**CONVERT(money,ISNULL((SELECT SUM(**{CostVariance}**) FROM tblCost c WHERE c.CostProjectKey = tblProjects.ProjectKey AND c.MasterKey IS NULL),0))