Tips for adding Days to your model for use on new Modeled/Cube sheets.
Assumptions this document is based on:
- Starting with standard calendar:Month -> Quarter -> Year
- Only adding days to existing calendar (no new year’s)
Note: When adding a new granularity to time like days, all years in your calendar must be updated (past and future).
Before you can modify time, your user role must be an administrator and have the Configure Calendar permission (which is not checked by default). To update this permission, navigate to Administration -> Roles and Permissions and edit your administrative role (whatever it is named).
Once you have the Configure Calendar permission you can navigate to Modeling -> Time, the first step is to export your current calendar, click the Export Calendar icon and open the Excel file.
Edit the Excel file:
- Add 3 columns between the Date* column and the first Code* column
- Copy or type the labels for the 3 rows (Code*, Label*, ShortName)
- Name the Stratum in the row below the labels, Day for example.Should look like the image below
There are a few things to consider for setting up the daily fields.
- The code you use should match what you expect from your data source when you are importing data
- Label is what you will see in sheets and reports, several options
- Short Date:1/1/14
- Day and date:Mon 1/1/14
- Short Name (optional) is used by Discovery Pro when displaying the days on charts, if left blank the Label is used
TIP: Adaptive Insights expects text fields in the import file for all columns except Date, on import Excel will convert anything that looks like a date in the other columns to Boolean values so you should use the Excel Text function to prevent this. Example where A1 has the date: =TEXT(A1,”m/d/yy”) would return the short date.
For this document, we will set the Code to the date and the Label to the short date.
- Formula for Column B:=TEXT(A6, “m/d/yyyy”)
- Formula for Column C:=TEXT(A6, “m/d/yy”)
- Column D will be left blank, during import Adaptive will default the value to the Label
- Copy the formulas down to all rows with a date
TIP: Before import, it is a good idea to highlight all columns except for A and set their type to Text.
Save the file then in Adaptive click the Import Calendar button.
Select Update, browse for your file and click Upload (DO NOT select Reload unless you have a blank model as it will erase all of your data)
Once complete you will now have days available to new Modeled/Cube sheets (or exiting Modeled/Cube sheets with no data) in your model. At the bottom of the Time screen you will see that your default stratum is still Month. Default stratum can only be changed if the model has no data.