Date Range on a Formula Column in a Widget
KB Article #:
85628
Summary:
Date Range on a Formula Column in a Widget
Description:
Unable to use the temporary date range function on a formula column in the table widget
Resolution: 
If the Formula the user is using in the column is based on a simple arithmetic process, then, in order to change the date range on that column, user will need to temporarily change the date range for the columns that are being used in the formula.  
 
Example:  
If user is using a project base table widget and the user is using columns for Total Contract Amount, Spent and Billed, user may add a Formula column for [Total Contract Amount] - [Spent] to determine the remaining amount to use. 
  • In this scenario, user can temporarily date range the Total Contract Amount and the Spent columns, but the user cannot for the Formula column.
  • Because of the nature of the Formula, the amount calculated is related to the two date ranged column amounts.
  • The user will not be able to solely date range the formula because in essence the input values must be changed first.
Note: If the user does not currently have one or more of those columns that the formula is referencing as part of the table widget, the user will need to bring the columns into the widget. 
Another possible solution is to add the date range into the column itself through the edit mode of the widget. The user would select the column to put the date range on and then apply the date range in the Properties. This would not be temporary, however, and the user would need to change this within the edit of the widget to change the date range.
 
Example:  If the user is using the Employee base in a table widget and wanted to pull in the Billable status hours an employee has entered in a given period of time, the user could use the formula: 
SELECT sum(tUnits) from AxTransaction WHERE tActivitytype = 1 AND tIsCurrent = 1 AND tEmployee = [Employee Key] AND tStatus = 0 AND tDate BETWEEN {startdate} AND {enddate} 
In this example, the users edit the widget and chooses the date range in the properties of the column and the formula would follow this date range without regard to the other columns in the widget.
 
Details  
 
To view full details, log in with your Deltek Support Center account.
Print