Configure Time Series

Time Series

The Time Series feature allows you to define a fixed span of time and view data within that time frame. You can then roll the fixed span forwards or backwards in time, seeing how the data changes. This means that you can move your inquiry “window” through historical data without having to reformat your inquiry, add columns, etc.

This feature allows you to define sets of columns whose sequence is significant as a ‘series’, and then add new columns to your inquiries that are based on that series, such as ‘Current Period’ and ‘Year to Date’. It enables you to add complex, time-based calculations, such as rolling 12 month average, to your inquiries.

The benefits to using a Time Series include the following:

• Avoid unwanted column expansion
• Retain formatting when changing filter selections such as period/year
• Automatically change value columns related to the current selection
• Easily create inquiries such as 12 month rolling values
• Make calculations more dynamic

While you can use any across-pivoted fields, the most common is to base the Time Series on the period/ year dimensions. The number of members selected within each dimension (data field) defines the number of item combinations included inside the Time Series set.

For example, a two dimensional series based on Quarters 1, 2, 3 & 4, Years 2014 & 2015 will produce 8 items (4x2). The earliest period/year defined in the Time Series would be first item in the series, so in this example it would be Q1 of 2014. The latest period/year defined in the Time Series is the last item in the series and in this example would be Q4 of 2015.

When designing reports for Viewer users, a Power User can add one or more Time Series sets to a report.

Time Series may be made dynamic. With dynamic Time Series it is not necessary to select the desired period using a filter. The Time Series will automatically open with the correct period and year already selected based on the associated selector values.

Time Series Example

In the following example, the Time Series is based on period and year dimensions and includes Q1, Q2, Q3 and Q4 of 2014 and 2015. We want to be able to see a certain quarter and the two previous quarters to compare them. In the Time Series wizard, we set the calculations like this:

The Time Series defaults into the Period Filter. Q3 2014 is the current item, so Q2 2014 and Q1 2014 display as well since they are the previous two items.

If you change the current item to be Q4 2014, the other columns that display are the 2 items previous to that, so Q3 2014 and Q2 2014.

Create a Time Series

To create a new Time Series:

1. From the Inquiry Objects menu in the Inquiry Objects group, click Time Series to open the Time Series dialog.

   

2. Click New Series to open the Create New Series wizard.

   

3. Click Next.
4. Enter a Series Name (unique name) and a Caption (which displays on the icon representing the Time Series within the Time Series dialog as well as on the filter if the Time Series is its own filter on the inquiry. Click Next.

   

5. Choose the dimensions you wish to use in the Time Series by double-clicking or selecting them and using the arrows to move them to the right panel. (The list of dimensions available includes all pivoted columns in your inquiry.)

   

6. Use the up and down arrows to position the dimensions with the most significant dimension being at the bottom of the list, the most significant dimension being the one that changes least when looking at the combination of dimensions. (In the case of period and year being the dimensions in the Time Series, period should be listed first and year should be listed second.) Click Next.
7. Define the members to include in the Time Series based on the chosen dimensions by double- clicking or selecting them and using the arrows to move them to the right panel.

   

8. Checkbox for Add new members when database opens - When checked, any new members added in your ERP system will be added to this list. For example, if this box is checked for the year dimension in your Time Series, whenever a new year is added to your ERP system, it will be added to this list. Note that as the dialog states, the order of the members cannot be changed when this option is enabled.
   To help explain the series calculations, the example below uses Period 5 2009 to be the Current Item or ‘marker’ within the Time Series.
   1. Current Item – creates a series calculation that displays the currently chosen point in the Time Series
   2. Next Item – creates a series calculation that displays the next point on the Time Series from the Current Item (using the above example, this would derive Period 6 2009) If you increase the number of associated steps, you will increase the number of series calculations created. (For example, if you set Next Item steps to 3, you will have 3 columns that would derive Period 6 2009, Period 7 2009 and Period 8 2009)
   3. Previous Item – creates a series calculation that will display the previous point on the Time Series from the Current Item (using the above example, this would derive Period 4 2009).
   4. Just like Next Item, if you increase the number of associated steps, you will increase the number of series calculations created. (For example, if you set Previous Item steps equal to three, you will have three columns that would derive Period 4 2009, Period 3 2009 and Period 2 2009.)
   5. Prior Item – creates a series calculation that displays a point that is x number of steps from the Current Item. (Using the above example, this would derive Period 5 2009 if the Prior Item steps were set to 12. However, if they were set to 6, it would derive Period 11 200.)
   6. Sum From Start – creates a series calculation that will sum all the values from the start of the year. (Using the above example, this would be Period 1 2009 to Period 5 2009.)
   7. Sum To End – creates a series calculation that will sum all the values from after the Current Item to the end of the year. (Using the above example, this would be Period 6 2009 to Period 12 2009.)
   8. Rolling Average –creates a series calculation that will take the average of the Current Item and the number of steps back that were specified. (Using the above example, this will average from Period 5 2009 back to Period 6 2008 if the Rolling Average steps were set to 11. However, if they were set to 4, it would take the average from Period 5 2009 back to Period 1 2009.

8. Using the up and down arrows, be sure that items are in the correct order. Click Next.
9. Specify default calculations to be included in your Time Series by checking the box to the left of the calculation type and entering the number of steps. For each selection, a value column will display in your inquiry. The caption for each series calculation can be modified here.

   

10. Click Next.

11. If required, make the time series a dynamic Time Series. The wizard will now show each of the Time Series dimensions for which at least one selector exists. The user can pick a selector to be used to determine the current item for that dimension

    

    
    Note that if none of the dimensions in the Time Series have any selectors defined, then this part of the wizard is not shown.
    The associated Time Series dimension value will automatically update when the report is opened to value specified by the selector. In other words, if a selector is chosen for a dimension, this will be reflected when viewing the current item of the Time Series. It will reflect the value of the selector, instead of defaulting to the first item in the Time Series.
    Leave all dimensions set to “No override” (the default setting) if the Time Series is not to be dynamic.

12. Click Next.
13. Click Finish.

Options in the Time Series Dialog

The Time Series dialog options include:

• New Series – Create a new Time Series

• Edit Series - Edit the name and/or the items being include in the time range (such as adding another year)
• Remove Series – Delete the Time Series permanently from the inquiry
• Copy Series – Copy a Time Series in order to create a new one based on an existing one. If desired, both can be added to the same report at once.

Tabs in the Time Series dialog

• Overview (see above) - General information about the Time Series, such as the current item, the number of items, named items and calculations contained in the Time Series set.
• Named Items – These items act as labels within the Time Series set and can be either absolute or relative to other named items. The Time Series wizard automatically creates as many named items as required to produce the common calculations you selected. You can create additional named items, edit exiting items or remove items.

  

• Calculations - All the calculations being performed in the Time Series based on the calculations you selected when creating the Time Series set.

  

Prior, previous, and next steps

Once the Time Series has been created, you can see the list of items within the Time Series set.

1. Go to the Time Series dialog.

2. Select your Time Series on the left and in the Overview tab, click on Change.

   

   
   This is where you can not only change what the current item or ‘marker’ is, but you can also see a list of all the items within the Time Series to know the number of steps backward and forward from the current item.

Change the current item or ‘marker’ in the Time Series:

There are a couple ways to change the current item on the Time Series:

1. Using the drop-down in the filter, change the item.
2. In the Time Series dialog, highlight the Time Series on the left, select Change and select the item you wish to be the current item.
   Once you change the current item or ‘marker’, you see that the other columns change according to the calculations you have selected in the Time Series.

   

Adding Additional Named Items to an Existing Time Series

A named item acts as a label for a point on the Time Series. The label can either be absolute, in which case it refers to a specific year and period in time (e.g. Period 5 2009), or relative, where it is relative to an existing named item (such as the special item labeled CURRENT).

To add additional columns (such as CURRENT-6 through CURRENT -1 to represent the previous 6 months) to a previously created Time Series:

1. From the Time Series dialog, go to the Named Items tab.
2. Click New.
3. Click Next to follow the wizard.

4. Enter a name for the item.

   

5. Choose whether it is absolute or relative.
6. Click Next.
7. If a relative item, choose which item to make this relative to and how many steps backward or forward to go from the related item.

   

   1. Change the name and caption, if desired.

      

   2. Click Next.
   3. Click Finish.

8. If an absolute item, choose the item to add to the Time Series.

   

   1. Click Next.
   2. Change the name and caption if desired (see above).
   3. Click Next.
   4. Click Finish.

Select Time Series columns for display in the inquiry

1. From the Time Series dialog, go to the Series Calculations tab.
2. Select the calculations to be used by using the checkboxes to their left.

Columns in the Time Series Showing ‘Out of Bounds’

If you are going out of the range of the time span you have set up, you will see ‘Out of Bounds’ on the column header.

Say, for example, the first period in your time span is Q1 of 2009 and you are showing the current item as well as the previous 2 quarters. If your current item is Q1 2009, you cannot show the previous two quarters since your time span begins with that year. Once you change the current item to Q3 2009, it will display the prior 2 periods, Q2 2009 and Q1 2009.

Adding a new Time Series Calculation to your Inquiry

Once the Time Series has already been created, you can add more calculations to the inquiry, such as Rolling 12 Month Average:

1. From the Time Series dialog, go to the Series Calculations tab.
2. Click on New. The Create Calculation dialog is then displayed.
3. Choose the calculation you wish to add and modify the associated information as needed:

   

   • Name
   • Caption
   • Additional setup (this varies depending on the chosen calculation)
4. Click OK to return to the main Time Series dialog.
5. Click OK to return to your inquiry.

Edit a Time Series Caption

1. From the Time Series dialog, go to the Series Calculations tab.
2. Select the calculation you wish to edit the caption on and click Edit. The Edit Calculation dialog is then displayed.

   

3. Change the caption. (e.g. add the static text ‘Current Time Series is’ in front of the default caption <<CURRENT>>).

In this example, the end result would look like this:

Time Series Filter

Once you create the Time Series, it will automatically appear inside the relevant filter field as a drop- down menu, the current item being the item that displays in the filter. Note that you may need to use the Visual Assist to remove the filter item that was there prior to the Time Series being configured.

Note: Once you click in the filter itself, the Visual Assist becomes available.

An extra button appears on the left side of the filter field. This button allows you to toggle between the Time Series drop-down list and the normal filter view. In addition, if you have multiple Time Series in an inquiry, you can set which is being used by clicking on this same button.

When in Time Series mode, use the drop-down list to set the current item for the Time Series:

Both Time Series and normal values can be de/selected by accessing the Visual Assist:

Separate Filter

A user with a Configurator license can add the Time Series set as a separate filter in Filter Layout. If you have more than 1 Time Series in an inquiry, each can have its own filter.

Time Series and User Defined Captions

Any user defined captions that are set up for items used in the Time Series will be used. For example, if you have set up the caption ‘January’ for Period 1, and this period is being used in the Time Series, the column will display as ‘January.’

Time Series Columns in Calculations

One of the benefits of using a Time Series column in a calculation is that it is dynamic; when you change the Time Series columns being displayed, the calculation is updated accordingly.

When defining the calculation, you can choose any of the Time Series items when you choose Calculations in the drop-down menu:

Time Series and RIO

A user with a Configurator License can add a Time Series set to RIO or Reusable Inquiry Objects, making it available to other users in order to use it in their inquiries.

Add a Time Series to RIO

To add a Time Series to RIO, perform the following steps:

1. Open the inquiry where the item exists.
2. Access RIO by clicking on Reusable Inquiry Objects from the Inquiry Objects menu.
3. Highlight the Time Series on the left panel to be moved into RIO.
4. Highlight the folder where you want it moved to on the right panel.
5. Click the arrow in the middle of the screen to move the item over to the right.

Subscribe to a Time Series in RIO

If a user with a Configurator license has added a Time Series to RIO, users can subscribe to it in their own inquiries. To subscribe to a Time Series that has been added to RIO:

1. Open the inquiry in which you want to subscribe to the Time Series.
2. Access RIO by clicking on Reusable Inquiry Objects from the Inquiry Objects menu.
3. Subscribe to the Time Series by checking the box to the left of it.
4. Click either Apply to apply the Time Series or OK to apply it and exit the dialog at the same time.

Unsubscribe Library Object Dialog

When the Time Series is subscribed to RIO, any change to it, including changing the current item, is considered a change to the RIO object. Therefore you must choose one of the three options when changing it:

1. Cancel the changes – Cancel changes you have made to the Time Series (including the choice of the current item or ‘marker’).
2. Unsubscribe the object from the Reusable Inquiry Objects and update this inquiry’s version only - Keep the change you have made to the Time Series and unsubscribe it from RIO, meaning other inquiries subscribed to the Time Series will not reflect any changes.
3. Keep the subscription and update the object in the Reusable Inquiry Objects with your changes - All inquiries subscribed to the Time Series will be updated with the changes you have made.

   

Note: Only a user with a Configurator License will have the 3rd option to update the RIO object. All other users will only see the first two options.