In the eFashion.unx universe, there is no object for number of years trading. The universe only has the Opening Date dimension object.
As you can see in the figure, the values returned by the Opening Date object are displayed in the format MM/dd/yy hh:mm:ss a.
To display the number of years that the stores have been trading, create a variable based on the opening date of each store.
If you point the cursor at the Opening Date object in the Show document objects tab, you see that it is a string.
To calculate the trading years, remove the time element from the string, and then convert it to a date type. Doing this allows you to perform a date calculation to determine the difference between today's date and the opening date.
You can use the Substr() function to remove the time element of the string. However, the date element is not in a constant format. For example, some days and months are expressed as single digits while others are two digits.
Use the Pos() function to define the number of character elements of the Substr() syntax. The Pos() function is effective because the date is always followed by a space. To identify the position of the space, create the Date_Substring variable.
When the time element is no longer present in the Opening Date object, create another variable to convert the Date_Substring values to date type using the Open_Date formula.
Formulas for Example Variables
Formulas for Example Variables
This table shows the formulas used in this concept and describes their purpose:
| Variable Name | Formula | Purpose |
|---|---|---|
| Date_Substring | =Substr([Opening date] ; 1 ; Pos([Opening date] ; " ") - 1 ) | To remove the time element from the Opening Date column |
| Open_Date | =ToDate([Date_Substring] ; "MM/dd/yy") | To convert the Date_Substring values to date type |
The date must be formatted in the same way as the string that is being converted. For instance, if the order of the dates in the string is Month/Day, then the date must be formatted the same way. If the order of the dates in the string is Day/Month, however, then the formula must use that format as well.
