I want to create a new date column. I have a date (business and non business day), i want to add 10 business days. Example today Saturday 12/3/2022 new column date 12/16/2022. Example today is Monday 12/5/2022 new column date 12/19/2022

[John Koger]

I want to create a new date column. I have a date (business and non business day), i want to add 10 business days. Example today Saturday 12/3/2022 new column date 12/16/2022. Example today is Monday 12/5/2022 new column date 12/19/2022

[Tushar Badlani]

Hi John,

You can make use of the DateAdd() function to add 10 days to the date column, please refer to the following documentation: https://docs.tibco.com/pub/spotfire/7.0.0/doc/html/ncfe/ncfe_date_and_time_functions.htm

[Fredrik Rosell]

Hello John,

Here's one way this could be done, using an R package - bizdays (https://www.rdocumentation.org/packages/bizdays/versions/1.0.12)- that could be used to add business days.

I added this as an Expression function (which is run in TERR - TIBCO Runtime for R), so it will show up just like other available function for use in calculated columns/custom expressions. 

---

1. Add the bizdays package

As this uses an R package - bizdays - that is not available by default, I installed the bizdays package.

Please refer to the manual for information on "TERR Tools - Details on Package Management"

https://docs.tibco.com/pub/sfire-analyst/12.1.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm#t=terr%2Fterr_details_on_package_management.htm&rhsearch=r%20package%20management&rhhlterm=r%20package%20management&rhsyns=%20

Note: If you want other users (and maybe even web client users) to be able to use the function, please refer to the manual for "Package Management for the TERR Service"

"https://docs.tibco.com/pub/terrsrv/1.13.0/doc/html/TIB_terrsrv_install/terrinstall/topics/package_management_for_terr_service.html. You can ignore that part now if you just want to get this working in your own Analyst for now.

---

2. Creating the expression function

Please refer to the manual for information on "How to create an expression function"

https://docs.tibco.com/pub/sfire-analyst/12.1.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm#t=df%2Fdf_how_to_create_an_expression_function.htm&rhsearch=expression%20function&rhhlterm=expression%20function&rhsyns=%20 

Here's my Expression function:

Name: DateAddBusinessDays

Description: (Empty in my example but you should add that)

Function Type: Column function

Return type: DateTime

Category: Date and Time functions

Script:

#Define the DateAddBusinessDays function.

#It takes two arguments - first the old date, then the number of business days to add

DateAddBusinessDays <- function(oldDate, businessDaysToAdd)

{

 #add business days to each date

 outcol <- bizdays::offset(oldDate, businessDaysToAdd, cal = business_calendar)

 #outcol is a date. Coming from TERR, Spotfire would treat that as a Real. 

 #Convert to POSIXct, to get DateTime in Spotfire. I used as.character to be able to set the timezone 

 outcolconverted <-as.POSIXct(as.character(outcol),tz = check_tzones(oldDate))

}

#See https://www.rdocumentation.org/packages/bizdays/versions/1.0.12 for information about bizdays

library(bizdays)

#create business calendar, setting saturday and sunday to the non-business days

business_calendar <- create.calendar('my_calendar', weekdays = c('saturday','sunday'))

output <- DateAddBusinessDays(oldDate = input1, businessDaysToAdd = input2)

 

---

3. Now try using the new expression.

I add a new calculated column using the following expression, to add 1 business day to the "Col2" column.

Date(DateAddBusinessDays([Col2],1))

Note: A DateTime is returned from TERR, so I used Date() to convert it to a Date. 

 

I hope this helps.