Follow

Calculated Field Examples

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

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)

111.jpg

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

111.jpg

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)

Issue_Management.jpg

Update / Creation History

  • Date on which project was created: (SELECT MIN(Created) FROM tblProjects s WHERE ISNULL(s.MasterKey,s.ProjectKey) = ISNULL(tblProjects.MasterKey,tblProjects.ProjectKey))
  • 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 ISNULL(s.MasterKey,s.ProjectKey) = ISNULL(tblProjects.MasterKey,tblProjects.ProjectKey) ORDER BY s.Created))
  • Updated by (person who last updated the record):(SELECT TOP 1 EmployeeKey FROM tblUsers u WHERE u.UserKey = tblProjects.UserKey)

Updated_By.jpg

Look-Ups

  • Look-up the project manager:(SELECT Manager FROM tblProjects s WHERE s.ProjectKey = IssueProjectKey)

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)

Is_User.jpg

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request
Article is closed for comments.
Start a 30 Day Free TrialClick ClickNo Credit Card and No Obligation
Powered by Zendesk