OneCloud has the ability to derive date and time values based on Runtime Variables. This allows for the utilization of 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 on Variable Transformation.
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. This article explores the Q2 forecast example.
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 the Variable Transformations will be based on the Base Time Period Variable value. The Base Time Period Variable is set to 2020-03 (March 2020). This example opts for the YYYY-MM format for the value of the Variable. This format is important to remember as the Variable Transformation is specified later.
Next, utilize the Variable in our Command. This example laid the groundwork to write transformed variable values to a file simply to verify our transformation. The file created uses the File Utilities BizApp Create File Command. This example will derive four values used in the forecast process - Start Period, Start Year, End Period, End Year. The same Base Time Period Workspace Variable has been assigned to each of the four values. The original value of the Base Time Period Variable has been printed for reference purposes.
Next, perform the Variable Transformation needed to derive each of the unique forecast process values. Start by applying the Parse Date/Time transformation.
To derive the Start Period value, first, specify the format of the time period which is parsing (1). Remember, the Base Time Period followed the YYYY-MM standard, so 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 assign the corresponding %b code (2).
🌟 The Coordinated Universal Timezone (UTC) is used 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.
Repeat this action for the Start Year, but modify the output to use the FYYY format because the application being integrated to uses this format for the Year as opposed to the YYYY format.
📓 Notice the combination of the text string FY with the variable value for the two-digit year (%y).
Next, create the End Period value. To derive the End Period, 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, perform the same parsing action that we did to create the Start Period value (2).
Finally, create the End Year value. As with End Period, to derive the End Year, first use an additional variable transformation to add 15 months to the Base Time Period variable value (1). 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, perform the same parsing action to create the Start Year value (2).
Finally, execute the Chain to see the result of the 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 email us at firstname.lastname@example.org.