This Analytics Edge wizard is used to pivot a column of data similar to Excel’s PivotTable. If you are pivoting by date, you can also select the date range and automatically fill in any missing values.
Pivot to Columns
This function is typically used to produce time trend reports, or to convert tabular data into a more usable form.
Arrange Columns to Keep – select and rearrange the columns you want to keep in the resulting table by position (column letter) or By Name.
Pivot to Columns – select a column to pivot, so a new column in the pivoted sheet will be created for each unique value in the selected column.
Ignore empty cells – if the pivoted column contains blank cells, ignore them and do not create a column with a blank name.
Ignore Case – when combining duplicate rows, ignore differences in upper or lower case.
Cell Values – select a column to be used for the values in the cells in the pivoted table.
Aggregation – select the aggregation to use when there are duplicate values for a specific cell position.
Fill empty cells with zero – by default, cells with no data will be empty. This options fills them with 0.
Pivot By Date Options
When pivoting by date, an extra tab appears with additional options.
Display format – select or enter code to determine how dates will be display in the column names. Note that column names are always written into Excel as text. Use the See Codes button to open online help document describing the available date codes.
Fill in missing values – for the date range and step size selected, fill in all of the columns even if there is no data.
Starting at – pick the date to be used for the start of the date range.
First date in the data – use the earliest date in the original column.
Specific date – select a date to use.
Duration – instead of a start date, pick and end date with a duration.
Rolling periods – enter the number and type of periods to display, to end at the ending date selected. Rolling periods can start and end in mid-calendar period — i.e a rolling week is 7 days ending at the end date selected, regardless of which day of the week it is.
Calendar periods – enter the number and type of periods to display, to end at the ending date selected. Calendar periods always start at the beginning of a calendar period.
Ending at – pick the date to be used for the end of the date range.
Last date in the data – use the latest date in the original column.
Specific date – select a date to use.
End of period – use the end of the selected period.
Blog article: Using the Pivot Function
Updated for v10.