Versions Compared
Key
- This line was added.
- This line was removed.
- Formatting was changed.
Enhance the analysis capabilities of Rolling Flexible Variance mode by adding customizing the columns in the grid. For example, you can add custom columns to the grid.
View the column configuration
Within a Profit and Loss or Balance Sheet statement, click the Columns > Customize Columns. The Customize Columns window displays a representation of the columns that are currently in the statement.
You can edit or delete the existing columns and add new columns, as outlined below.
Change the number format
Add a calculated column
Use calculated columns to perform custom , 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.
Image AddedThe following screen recording quickly demonstrates how to add a calculation column and take several actions with it.
Calc column.mp4Change 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
You can add custom calculation columns to perform calculations based on data within the grid.
Select the applicable dimensionTake advantage of the inbuilt functions to quickly create formulas for your calculation columns.
Identify where you want the new column to sit. The
A new column is always inserted to the right of the column from which you add it.
If you add a calculation 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 that will sit on to the left of where you want to add the new column and select Insert new calculated to the right. The new column is added to the right.Click the fx button in the column header to put click Add calculation column.
Right-click the new column's header and select Edit formula. This action puts the fx bar into edit mode, then proceed to enter a formula or function.
(Optional) Change the default name of the new column to something more meaningful.
Save your new grid layout as a favorite.
Add a measure column
TO DO
Edit the name of a custom column
Right-click the column header and select Edit header, then type the new name in the white box and press Enter.
Hide a column
This option is useful if you are using a Variance.
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 other spreadsheet software, such as Microsoft Excel or Google Sheets. 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.
Image AddedProceed to manage the column, such as giving it a more meaningful name or changing its number format.
(Optional) Save your view as a favorite.
See the video screen recording above for a quick demonstration or expand the following example for detailed steps.
Expand | ||
---|---|---|
| ||
This example shows how to add a column to calculate the working day equivalent values for the Period 1 column in the grid.
The following image show how the formula is built step by step. You can see that when you reference column in a formula, a chip displays with the column name. The chip has the same color as the column border so you can clearly see the link. This formula uses the Working Days function in which there are two calendars to choose from. When the second calendar called Working Days is selected, it displays as calendar number 2 in the formula. Image Added |
Expand | ||
---|---|---|
| ||
See this Use formulas page for help. Although it’s a Budgets & forecasts page, a lot of the information is applicable here, and many of the functions are the same. Some functions unique to Flexible Variance mode are: ARRAY Function: ARRAY(value1, [value2…]) Groups multiple values into a single, ordered list for use as a parameter within other functions.
COALESCE Function: COALESCE(value1, [value2…]) Description:
COUNT Function: COUNT(coordinate1, [coordinate2…]) Description:
FORECAST LINEAR Function: FORECAST.LINEAR(position, series) Description:
PERCENTILE Function: PERCENTILE(series, k) Description:
PERIOD COUNT Function: PERIODCOUNT(coordinate) Description:
|
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 .
Move a column
Click and hold the column’s Move button on the left side of the name (a blue box displays around the row), then drag the item left or right to its new positioncolumn. To unhide the column, click the blue line where it’s hidden between the other two columns.
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.
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.
On this page
Table of Contents | ||||
---|---|---|---|---|
|