In the eFashion universe, you do not have an object that shows the number of years a store has been trading. You only have the opening date dimension object.
The opening date object returns values in the format MM/dd/yy hh:mm:ss a, as shown in the figure.
To display the number of years a store has been trading, create a variable based on the opening date of each store.
When you point the cursor at the opening date object in the Objects tab, you see that it is a string.
To calculate the trading years, first remove the time element from the string. Then, convert the string to a date type. This allows you to calculate the difference between today's date and the opening date.
Functions: Substr and Pos
You can use the Substr function to remove the time element from the string. However, the date element does not have a constant format. For example, some days and months use one digit, while others use two digits.
Use the Pos function to find the position of the space that follows the date. This helps you define the number of characters to use in the Substr function.
To do this, create a variable called Date_Substring:
=Substr([Opening date];1;Pos([Opening date];"")-1)
After you remove the time element from the opening date object, create another variable to convert the Date_Substring values to a date type.
Use the Open_Date variable for this step:
=ToDate([Date_Substring];"MM/dd/yy")
The date must be formatted in the same way as the string you are converting. For example, if the string uses the Month/Day order, the date must use the same order. If the string uses the Day/Month order, the formula must use that format as well.
Date Calculations
After you create a date variable for the store opening date, you can find the number of days between the opening date and today by using the CurrentDate and DaysBetween functions.
Use this formula to create a variable named Trading_Years:
=DaysBetween([Open_Date] ; CurrentDate()) / 365.25
Divide the number of days by 365.25 to convert days to years. This value accounts for leap years.
For more information about this topic, please read SAP BusinessObjects Web Intelligence User's Guide
Let's Summarize What You've Learned
- Remove the time element from the opening date string using the Substr() and Pos() functions.
- Convert the cleaned date string to a date type with the ToDate() function.
- Ensure date formats in formulas match the format of the original string data.