Working with Time Hierarchies

When working with time hierarchies, the expected time parts are known in a way that is usually not applicable for other types of hierarchies. Therefore, there are some additional operations available that can help you working with time data.

Sometimes you might work with data where some category values are missing. If the visualizations make use of a Date, Time or DateTime column and you like to present the data in an aggregated form, missing data can have strange effects on your calculations. For example, below is a cumulative sum of sales for a few years, where data for three quarters are missing:

In this example, you might want the bars for the missing quarters to be of the same size as the last available bar rather than completely missing. To do this, you can use Compensate for missing values in the visualization properties. This functionality is available in the bar chart, the line chart, the combination chart (from the installed client only), the waterfall chart, and the KPI chart. It introduces a gap-filling mechanism where empty values can be temporarily inserted for the missing rows. This way, the cumulative sum can instead show the same value as the last available bar for the bars with missing data:

In the installed client, you reach Compensate for missing values via the Appearance page.

Note: Compensate for missing values only works with true Date, Time or DateTime columns. The check box will have no effect on a hierarchy made by putting together string values from different columns (such as Year, Month and Day).

Data values in general can be valid values (non-null and non-error), invalid values (errors, such as a value of a different data type than the rest), or empty values (where the row identifier is there, but there is no value in the value column). There might also be completely missing categories. For most kinds of data, this can be difficult to handle, but time-series can be filled out with the missing data points, because it is always possible to calculate which time points should have been available. Spotfire can temporarily add the missing rows and fill them with empty values so they can be used in calculations.

When Compensate for missing values is used, the max and min values in the time column are determined. Then all missing steps, depending on the time hierarchy level, are added.

Multiple date/time parts on different axes can be combined in a visualization as long as the same date/time column is used.

When categorical time values are used in a visualization, you can control what to show using the Categories setting on the axis. If you want to completely hide those categories where no data is available (either due to filtering or to missing data) this can be done by selecting the Show Filtered Values setting:

In the first image on this topic, there is an example of a visualization with missing values where no compensation for missing values has been made, and the Show Filtered Range setting has been selected. This option only hides empty categories on each side of the range, but not in the middle. There is also an option to Show All Values where the currently filtered-out values on the sides also remain visible.

Change the setting in the Advanced Settings dialog or by right-clicking on the visualization axis and selecting Categories and one of the options from the pop-up menu.

Mixed hierarchies with both date and/or time parts (from the same datetime column) and other categories are supported by treating the categories as part of the group by hierarchy. The combinations to show are specified in the Advanced Settings dialog or using a custom expression.

If the range of the date/time column is significantly larger than the step size, a huge number of rows might be needed when compensating for missing values. For example, this will be the case when the step size is millisecond and you have dates spanning several years. For performance reasons a property limit is defined, determining the max number of rows that can be inserted. The MaxMissingTimeParts property can be changed under File > Document properties, Properties tab. An administrator can also change this preference by going to Tools > Administration manager, Preferences tab, and then clicking on DataOptimizationPreferences under DataOptimization and editing the MaxMissingTimeParts preference.

Note that the limit for max number of rows to add is based on the size of the span and not on the actual missing values. This means that if the preference value is set too low, you might encounter this limit even though not that many time parts were missing in the current setup. If the message "Could not compensate for missing values. The document property value for MaxMissingTimeParts has been exceeded." is encountered, you might want to increase the property value, but the solution can also be to clear the Compensate for missing values check box or simply to try to reduce the granularity on the time series axis by using a hierarchy slider (if one is available).

When a visualization item based on temporarily added rows is marked it will look like a regular marking in the visualization. However, because no real rows are marked, this marking will not be propagated to any other visualizations nor to the Details-on-Demand.

A time hierarchy is actually built using the BinByDateTime function. This function uses three arguments: 1) The Date, Time or DateTime column, 2) the hierarchy definition as a string of the date parts separated by dots, and 3) the pruning level (meaning which level to start the hierarchy slider position on). For example, BinByDateTime([Column],"Year.Quarter.Month.Day",2)

For Date or DateTime columns you have the option to show hierarchies based on the ISO 8601 standard by using the ISOYear and ISOWeek date parts, rather than the regular year and week date parts. According to ISO 8601, Monday is always the first day of the week, and the first week of a year is the week that contains 4 days or more from the new year. An ISO week-numbering year contains 52 or 53 full weeks.

Use an ISO week hierarchy by defining a BinByDateTime expression similar to BinByDateTime([Column],"ISOYear.ISOWeek",1), or select the ISO Year >> Week hierarchy directly from an axis, as seen on Hierarchy Slider.

Note: The Year, Week, and YearAndWeek methods depend on regional settings, and might produce strange results for values crossing a year boundary (for example, you might see week 53 for some days when it should have been week 1). To get predictable results, use BinByDateTime([Column],"ISOYear.ISOWeek",1) or the separate ISOYear/ISOWeek methods instead.

Similarly, a time span hierarchy is built using the BinByTimeSpan function. Binned time spans can be useful, for example, when viewing groups of intermediate times in a sporting context or when analyzing log data. This function uses three arguments: 1) The TimeSpan column, 2) the hierarchy definition as a string of the time span parts separated by dots (e.g., "Hours.Minutes"), and 3) the pruning level (meaning which level to start the hierarchy slider position on). For example, BinByTimeSpan([Column],"Days.Hours.Minutes.Seconds",2)