Manage columns (Flex Modes)

Enhance the analysis capabilities of Flex Modes by customizing the columns in the grid. For example, you can add custom columns, change the number format, or hide columns. These actions are accessible via the menu button in the column’s header and when you right-click the column’s header.

The following screen recording quickly demonstrates how to add a calculation column and take several actions with it.

Change the number format

You can change the format of the numbers displayed in any of the numeric (measure) columns in the grid.

Right-click the column header and select Number format, then select the required format from the list.

Add a calculation column

Take your analysis further by adding columns to perform custom calculations based on data within the grid. You can use basic arithmetic or write more complex formulas that reference other columns, or a cell in the summary row. You can also take advantage of the inbuilt functions to quickly create formulas for your calculation columns.

It's easy to identify calculation columns, as they have an fx indicator in their headers. The default calculation columns have a grey fx, and any custom calculation columns you add will have a blue fx.

You can click any cell in these columns to view the underlying formula in the fx bar. The formulas include a description of the referenced columns rather than just a reference to a cell number or coordinate, so you can easily see how the values are calculated.

For example, in the following image, the first cell in the Variance column is selected. As a result, the components of the formula display in the fx bar, where the referenced columns and cell are displayed as chips. The chips have the same color as the corresponding cell borders, so you can clearly see the links.

1. Identify where you want the new column to sit. The new column is inserted on the right side of the column from which you add it. If you add the column from any of the breakdown period columns, a new column will be added for each one of those breakdown columns and the same formula will be applied to each one.

2. Right-click the column to the left of where you want to add the new column and click Add calculation column. The column is added to the grid and the fx bar switches to edit mode.

3. Click in the fx bar and enter a formula or function for the calculation, then press Enter. The calculation results are displayed in the new column.

   • You enter formulas in a similar way to how you do it in Budgets & Forecasts, and other spreadsheet software, such as Microsoft Excel or Google Sheets.

   • You can refer to other columns in the grid and cells within the summary rows.

   • To use a function, either click the blue down arrow next to the formula box and select a function from the list, or start typing the name of the function you want to use. As you type, a list of function names displays based on what you've entered so far.

   • Read this page to learn about the functions.

4. (Optional) Proceed to manage the column, such as giving it a more meaningful name or changing its number format.

5. (Optional) Save your view as a favorite.

Add a measure column

The measure column is available when the database has multiple streams and measures. It allows you to enhance the analysis capabilities in the grid by adding a column of numerical data. This data can come from another stream (first image below) or the same stream, but be a different measure to what's currently displayed in the grid (second image below).

In addition to simply adding more data to the grid for reference purposes, measure columns offer a more intuitive way to use the advanced filter, and when used with calculation columns, allow you to make more powerful calculations.

The measure column has its own definition, so it's independent of the Stream and Measure menus in the toolbar. It uses the same period as the column from which you add it, therefore you can change the period using the Period menu as usual.

1. Identify where you want the new column to sit. The new column will be inserted on the right side of the column from which you add it, and it will have the same period as that column. If you add the column from any of the breakdown period columns, a new column will be added for each one of those breakdown columns and the same definition will be applied to each one.

2. Right-click the column to the left of where you want to add the new column and click Add measure column.

3. Right-click the new column's header and select Define column. This switches the fx bar into a toolbar containing Stream and Measure menus that apply to the new column, as shown in the images above.

4. Select the required stream and/or measure. The data in the column updates immediately. Expand the section below for an example of using a Count measure.

5. Click a cell in the grid to close the column's definition.

6. (Optional) Proceed to manage the column, such as giving it a more meaningful name or changing its number format.

7. (Optional) Save your view as a favorite.

Example: Add a count measure

The Count option allows you to insert the relative count of items in a specific dimension.

Suppose you want to count the product item classes against each customer.

Add a measure column to the grid following the steps above. In the column definition, select Measure > Count, then select the applicable dimension (Primary Class in this example).

Hide a column

Hiding a column is useful if you create a more complex calculation that requires the creation of multiple calculation columns, and you only want to see the results. For example, if you create a variance column but do not want to display the two underlying columns.

Right-click the column and select Hide column. The blue vertical line indicates there's a hidden column.

To unhide the column, click the Unhide button at the top of the blue line.

Resize columns

You can change the width and position of most* columns in the nested grid to view all the important information in the optimal position. Hover over the column header to display the blue line and resize icon, then click and drag that icon to resize the column. The new column size persists after you refresh the data.

* Currently, the Code and Name columns are a fixed width.

Reorder columns

You can reorder the columns within a column group and move the Breakdown group before the Comparison group. Click, drag and drop a column header into its new location. The new column size persists after you refresh of data.

Edit the name of a column

You can change the name of any columns you add to the grid to give them a more meaningful description. This is important if you save and share your view as a favorite, as it lets others know what data is in the column.

Right-click the column header and select Rename column, click in the white box and type the new name, then press Enter.

Chart a column

See View your data in a chart.

Delete a column

You can delete any columns you add to the grid.

Right-click the column and select Delete column. The column is deleted instantly. You can click the Undo button if you accidently delete a column.