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 columns’s header and when you right-click the column’s header.
The following video 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 measure columns in the grid.
Right-click the column header and select Number format, then select the required format from the list.
Add a measure column
TO DO
Add a calculation column
Use calculation columns to perform custom calculations based on data within the grid.
Identify where you want the new column to sit. A new column is always inserted to the right of the column from which you add it.
Right-click the column to the left of where you want to add the new column and click Add calculation column.
Enter the formula for the calculation: Click the new column's menu button and select Edit formula. This action puts the fx bar into 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.
Proceed to manage the column, such as giving it a more meaningful name or changing its number format.
See the video and steps below for more information.
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. To unhide the column, click the blue line where it’s hidden between the other two columns.
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 position.
Edit the name of a column
You can change the name of any columns you add to the grid.
Right-click the column header and select Edit header, then type the new name in the white box and press Enter.
Delete a column
You can delete any columns you add to the grid.
Right-click the column and select Delete. The column is deleted instantly.
Example WIP
In its basic form, Flexible Variance mode allows you to compare two periods.
Example 1: Compare this year’s Sales Rep performance with last year’s, focusing on the performance in the last three months
For example, suppose you want to compare the current Flexible Variance 12 months with the same months last year but focus on what’s been happening in the last 3 months.
Click Period 1 > Flexible Variance 12 months.
Click Period 2 > Flexible Variance 12 months.
Click Period (breakdown) > Last 3 months.
(Optional) Add sparklines.
The grid updates to show the data for your selected periods and automatically calculates the variance between period 1 and period 2.
Example 2: Compare the Black Friday sales in the last two years, focusing on the three months leading up to that period.
As Black Friday falls on a different day every year, TO DO
For more advanced analysis, add levels or custom columns to the grid.]
Select the applicable dimension.
Identify where you want the new column to sit. The column is inserted to the right.
Right-click the column that will sit on the left of the new column and select Add calculation column. The new column is added to the right.
Click the fx button in the column header to put 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.
Example:
Continuing wiht the Black Friday example, add calculation columns to calculate the sales by day, and working day.
Save as a favorite.