Unpivoting

A common alternative format for time series data sets is as follows:

 

Where the first column represents the Date/Time, and subsequent columns represent the same variable such as Price for a given item.  In the MS Excel screen shot above, the price history for a series of stocks are displayed.

By default, this format cannot be used within Panopticon, as it expects each item to occur on a different row, with each variable (such as Price) occupying a single column.

The format is in fact a pivoted version of the format that Panopticon requires.

In general, when unpivoting, individual columns are being converted into additional rows with only two columns, by default named Measure and Value.

Steps:

1.     To unpivot the data, click the Transform Settings button on the Data Sources Setting pane.

The Transform Settings pane displays.

2.     Click  .

The Transform Settings pane changes to display the Unpivot Settings.

 

3.     Tap the Unpivot slider.

The Transform Settings button and Unpivot tab change to  and   , respectively and all of the columns are moved to the Unpivot box.

 

You may opt to move fields from the Columns to Unpivot box, or vice versa, using the following buttons:

·          - move all fields from the Columns to Unpivot box

·          - move all fields from the Unpivot to Columns box

·          - click after selecting one or more fields from the Columns box to move to the Unpivot box

·          - click after selecting one or more fields from the Unpivot box to move to the Columns box

You can also filter the list of columns by entering a text in the Search Columns search box.

4.     Give appropriate names to the Measure and Value columns.

For example:

Measure Column = Return Type

Value Column = Return Value

5.     Define the display formats for numeric fields. The default setting is: #,##0.00

6.     Refer to Enable Time Series Analysis for more information in enabling this feature.

Enabling the time series analysis when you perform an unpivot solves the problem of having to specify all of the values. It also allows you to choose which Time column should be used to specify the time series.

7.     Click .