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 Designer (Desktop), 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 Designer (Desktop) requires.

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

Unpivoting Data

1. To unpivot the data, click Transforms in the Data Preview area.

   The Datasource - Tranforms dialog displays.

2. Select the Transforms to Unpivot.

 

3. Check the Unpivot box.

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:

1. • - 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. Measures in Unpivot box may be sorted by dragging and dropping them to the desired order (moved up or down in the list).

Consequently, the order of the measures will determine the default order of the newly created dimension.

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

   For example:

   Measure Column = Return Type

   Value Column = Return Value

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

7. 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.

8. Click OK.