Manage columns in Flexible Variance mode
PreviousAdd levels to the grid in Flexible Variance mode (nest view)NextSave and share a favorite in Flexible Variance mode
Last updated
Last updated
Enhance the analysis capabilities of Flexible Variance mode 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.
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.
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 (TOTAL) 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. 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.
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.
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.
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.
(Optional) Proceed to manage the column, such as giving it a more meaningful name or changing its number format.
(Optional) Save your view as a favorite.
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.
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.
Right-click the column to the left of where you want to add the new column and click Add measure column.
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.
Select the required stream and/or measure. The data in the column updates immediately. Click a cell in the grid to close the column's definition.
(Optional) Proceed to manage the column, such as giving it a more meaningful name or changing its number format.
(Optional) Save your view as a favorite.
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. To unhide the column, click the blue line where it’s hidden between the other two 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.
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.
For optimal results, first filter the data to get a subset for the chart. Then click the header of the column you want to chart and click Chart column. The Chart panel opens on the right. You can then change the chart type and configure the chart.
This feature is still in development. It's quite different from how you create charts in the other modes. It's more similar to the charting tool in the Financial Statements and Budgets & Forecasts modules. See View financial information in a chart for related information.
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.
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.
