Time dimensions are used to filter data against time. For instance if you wanted to see sales for a specific period (this year, this month, last quarter), then you would need to create a time dimension. Time dimensions are always linked to a time or date in the data (for instance sell date, purchase date, expiry date etcetera). When you import a table or file which has a field which Vizubi recognizes as being in date/time format, Vizubi will automatically create a time dimension linked to that column. Data in that table or linked to that table can then be filtered against the time dimension.
Loading your database
Load one of the databases used in the previous lessons:
(1) Click on the Vizubi tab in the ribbon.
(2) Click on the Open button in the Database group.
(3) Browse until you find “C:\Documents and Settings\All Users\Documents\Vizubi\SampleData\” then select “getting started.ndb”.
(4) Click on the Open button to load the file.
Open the Vizubi Editor
(1) Click on the Edit button in the Database group of the ribbon. This will open the Vizubi Editor.
Open the New Time Dimension window
(1) Click the Time Dimensions button in the Navigation Pane.
(2) Click the New Time Di mension in the New group. The New Time Dimension window opens.
(3) The new time dimensions already contains components as default (like year, month, day and other components commonly used).
(4) Leave the flag on Create Panel check box, if you want to create a panel with all the components in the time dimension. See Help for more details on Panels.
Naming the time dimension
(1) Insert a Name (no spaces or special characters).
(2) Add a Label and/or Description.
Ranges in time dimension
(1) Click on the Ranges button.
(2) The bottom box now shows the formulas used to calculate the time dimension.
(3) Double click on LY (Previous Year).
(4) This opens the Customize Range window.
Copying a formula
(1) Copy the string in the Formula box. Then click on Cancel to close the window.
(2) Click on the Add button in the Ranges group of the window.
Modifiyng a formula and creating a new component
(1) A new Customize Range window opens.
(2) Add TYA as Name and Two years ago as Label.
(3) Paste the string copied previously inside the Formula box and change it to d_year = Nbr(year(%now%)) – 2. This will make the time period of reference for this time dimension to two years before “this year” (if this year is set to 2009, TYA will show data from 2007).
(4) Click on OK to confirm and insert the new component.
Saving the new time dimension
(1) The new component you created has been added to the Time Dimension window.
(2) Insert OrderDate in the Formula Box.
(3) Click the Save and Close button in the Actions group to save the new time dimension (click Save and New if you want to save your time dimension and create another one as well).
View results in Vizubi Editor
(1) The new time dimension has been added to the list in time dimensions box.
(2) If you click on the Manage Tables button in the Navigation Pane of the ribbon you will see that a new table (Two_years_ago_Sales), has been added.
(3) Click on Switch to Workbook to go back to Excel.
How to use the new time dimension
(1) Click the upper part of the Report button in the Reports group of the ribbon. The Select Report window opens.
(2) Double click on “My first report” (previously created in Getting Started 1: My first report).
(3) The report opens in the worksheet.
Add a new field in our first report
Suppose you wanto to see sales by country not only for this year and the previous but for two years ago.
(1) Drag and drop TotalPrice from Field Section to Values pane in the Area Section.
(2) The field is add as a new column in the report.
(3) Click on Sum of TotalPrince in the Values pane and then on Expression settings… in the dropdown menu.
Set the reference period for the new field
(1) Click on the Define Period tab in the Expression Settings window.
(2) In the Dimensions pane (on the left side of the window) click on Two_years_ago_Sales.
(3) In the Period pane on the right, click on Two years ago.
(4) In the Custom Name box insert the name TotalPrice of Two years ago.
(5) Click on OK to confirm settings.
View the report and save it with a different name
(1) Now the new column shows data from two years before “this year” and the report shows sales by country for three different years.
(2) Click on the Save as button in the Reports group of the ribbon. The Save as window opens.
(3) Insert SalesbyCountry_last_two_years in the Name and Label box.
(4) Click on OK to confirm savings.
Time dimensions are used to filter data against time. For instance if you wanted to see sales for a specific period (this year, this month, last quarter), then you would need to create a time dimension. Time dimensions are always linked to a time or date in the data (for instance sell date, purchase date, expiry date etcetera). When you import a table or file which has a field which Vizubi recognizes as being in date/time format, Vizubi will automatically create a time dimension linked to that column. Data in that table or linked to that table can then be filtered against the time dimension.
Loading your database
Load one of the databases used in the previous lessons:
(1) Click on the Vizubi tab in the ribbon.
(2) Click on the Open button in the Database group.
(3) Browse until you find “C:\Documents and Settings\All Users\Documents\Vizubi\SampleData\” then select “getting started.ndb”.
(4) Click on the Open button to load the file.
Open the Vizubi Editor
(1) Click on the Edit button in the Database group of the ribbon. This will open the Vizubi Editor.
Open the New Time Dimension window
(1) Click the Time Dimensions button in the Navigation Pane.
(2) Click the New Time Di mension in the New group. The New Time Dimension window opens.
(3) The new time dimensions already contains components as default (like year, month, day and other components commonly used).
(4) Leave the flag on Create Panel check box, if you want to create a panel with all the components in the time dimension. See Help for more details on Panels.
Naming the time dimension
(1) Insert a Name (no spaces or special characters).
(2) Add a Label and/or Description.
Ranges in time dimension
(1) Click on the Ranges button.
(2) The bottom box now shows the formulas used to calculate the time dimension.
(3) Double click on LY (Previous Year).
(4) This opens the Customize Range window.
Copying a formula
(1) Copy the string in the Formula box. Then click on Cancel to close the window.
(2) Click on the Add button in the Ranges group of the window.
Modifiyng a formula and creating a new component
(1) A new Customize Range window opens.
(2) Add TYA as Name and Two years ago as Label.
(3) Paste the string copied previously inside the Formula box and change it to d_year = Nbr(year(%now%)) – 2. This will make the time period of reference for this time dimension to two years before “this year” (if this year is set to 2009, TYA will show data from 2007).
(4) Click on OK to confirm and insert the new component.
Saving the new time dimension
(1) The new component you created has been added to the Time Dimension window.
(2) Insert OrderDate in the Formula Box.
(3) Click the Save and Close button in the Actions group to save the new time dimension (click Save and New if you want to save your time dimension and create another one as well).
View results in Vizubi Editor
(1) The new time dimension has been added to the list in time dimensions box.
(2) If you click on the Manage Tables button in the Navigation Pane of the ribbon you will see that a new table (Two_years_ago_Sales), has been added.
(3) Click on Switch to Workbook to go back to Excel.
How to use the new time dimension
(1) Click the upper part of the Report button in the Reports group of the ribbon. The Select Report window opens.
(2) Double click on “My first report” (previously created in Getting Started 1: My first report).
(3) The report opens in the worksheet.
Add a new field in our first report
Suppose you wanto to see sales by country not only for this year and the previous but for two years ago.
(1) Drag and drop TotalPrice from Field Section to Values pane in the Area Section.
(2) The field is add as a new column in the report.
(3) Click on Sum of TotalPrince in the Values pane and then on Expression settings… in the dropdown menu.
Set the reference period for the new field
(1) Click on the Define Period tab in the Expression Settings window.
(2) In the Dimensions pane (on the left side of the window) click on Two_years_ago_Sales.
(3) In the Period pane on the right, click on Two years ago.
(4) In the Custom Name box insert the name TotalPrice of Two years ago.
(5) Click on OK to confirm settings.
View the report and save it with a different name
(1) Now the new column shows data from two years before “this year” and the report shows sales by country for three different years.
(2) Click on the Save as button in the Reports group of the ribbon. The Save as window opens.
(3) Insert SalesbyCountry_last_two_years in the Name and Label box.
(4) Click on OK to confirm savings.