Date Time Variable Transformation to support Business Processes
OneCloud has the ability to derive date and time values based on Runtime Variables. This provides the ability to utilize the System data and time or the Chain execution date and time to parse a particular date field, such as year or month) at the time of execution. This is outlined in this knowledge base article; however, there are instances where the date and time of the system or Chain execution do not systematically align to the date or time information needed within a given process. A rolling forecast process is a common use case because the start and end period of the forecast shifts depending on the forecast cycle. For example, a 16 month rolling forecast will span March of the current year through June of the following year for the Q2 forecast but September of the current year through December of the following year for the Q4 forecast.
If a set of time period variables is needed to support business-specific time periods that cannot be tied to a system calendar, OneCloud suggests the use of a Workspace or Environment Variable or set of Variables, to streamline the derivation of different business-related time periods that drive processes in the applications to which OneCloud is integrating. In this article, we will explore the Q2 forecast example.
We create a Workspace Variable called Base Time Period. This variable is the anchor that will be used to determine the start and end period and year of the forecast. Each of our Variable Transformations will be based on the Base Time Period Variable value. The Base Time Period Variable is set to 2020-03 (March 2020). We used the YYYY-MM format for the value of the Variable. This format is important to remember as we specify our Variable Transformation later.
Next, we utilize the Variable in our Command. In this example, we have laid the groundwork to write the transformed variable values to a file simply to verify our transformation. We are creating a file using the File Utilities BizApp Create File Command. For this example, we will derive four values used in the forecast process - Start Period, Start Year, End Period, End Year. Notice that we have assigned the same Base Time Period Workspace Variable to each of the four values we will derive. We have also printed the original value of the Base Time Period Variable for reference purposes.
Next, we perform the Variable Transformation needed to derive each of the unique forecast process values. We start by applying the Parse Date/Time transformation.
To derive the Start Period value, first, we specify the format of the time period which we are parsing (1). Remember, the Base Time Period followed the YYYY-MM standard so we utilize the format of the guide found in the Variable Transformation knowledge base article. Second, the Start Period uses the shortened month name (e.g., Mar) so we assign the corresponding %b code (2).
Notice that we utilize the Coordinated Universal Timezone (UTC) for all Variable Transformations. This is the best practice when performing date and time operations on dates that are not directly tied to the system date and time.
We repeat this action for the Start Year but modify the output to use the FYYY format because our application with which we are integration uses this format for the Year as opposed to the YYYY format. Notice that we combine the text string FY with the variable value for the two-digit year (%y).
Next, we create the End Period value. To derive the End Period, we first use an additional variable transformation to add an offset of 15 months from the Base Time Period variable value since our forecast window is 16 months (1). Then, we perform the same parsing action that we did to create the Start Period value (2).
Finally, we create the End Year value. As with End Period, to derive the End Year, we first use an additional variable transformation to add 15 months to the Base Time Period variable value (1). We utilize months as the unit of measure as opposed to years to account for situations where adding months to the Base Time Period could result in the End Year being two years from the current. For example, if the Base Time Period was December, then the End Year would be two years after the Start Year. After the month addition to the Base Time Period, we perform the same parsing action that we did to create the Start Year value (2).
Finally, we can execute our Chain to see the result of our Variable Transformation.
We hope that you find this article useful for understanding how Variable Transformation can be utilized to support your business processes. If you have any questions, please feel free to post them here or email us at email@example.com.