Pivot

pivot-bynameThis Analytics Edge wizard is used to pivot a column of data similar to Excel’s pivot table. If you are pivoting by date, you can select the date range and automatically fill in any missing values.

This function is typically used to produce time trend reports, converting 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 Name – select the columns by the name of the column instead of selecting them by column position (A, B, C, etc).

Move Up or Down – move the selected column up or down in the list.

Remove – remove the selected columns from the list.

Ignore Case – when combining duplicate rows, ignore differences in upper or lower case.

Reset – reset the list of columns to the original.

Pivot to Columns – select a column to pivot, so a new column will be created for each unique value in the selected column.

Ignore blanks – if the pivoted column contains blank cells, ignore them and do not create a column with a blank name.

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.

pivot2When pivoting by date, you get extra options, allowing you to set the date range to be displayed, the resolution per column, the format of the column name, and whether to fill in missing values.

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.

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

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 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 in version 2.5.1