Versions Compared

Old Version 4

changes.mady.by.user Denise McGettigan

Saved on

compared with

New Version Current

changes.mady.by.user Denise McGettigan

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
image-20240813-050749.pngImage Removed

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 column’s header and when you right-click the column’s header.

image-20240813-050749.pngImage Added

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

Calc column.mp4

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 measure column

TO DO

Add a calculation column

Use You can add custom calculation columns to perform custom calculations based on data within the grid. Take advantage of the inbuilt functions to quickly create formulas for your calculation columns.

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

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

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

  3. Enter the formula for the calculation: Click Right-click the new column's menu button header and select Edit formula. This action puts the fx bar into edit mode.

  4. 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-20240818-222705.pngImage Added

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

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

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 columnsscreen recording above for a quick demonstration or expand the following example for detailed steps.

Expand
titleExample: Use a function in a formula

This example shows how to add a column to calculate the working day equivalent values for the Period 1 column in the grid.

  1. Right-click the first comparison column

and select Hide. To unhide the

  1. (Period 1) and click Add calculation column.

  2. Right-click the new column’s header and select Rename 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.

  1. Click Period 1 > Flexible Variance 12 months.

  2. Click Period 2 > Flexible Variance 12 months.

  3. Click Period (breakdown) > Last 3 months.

  4. (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.]

  1. Select the applicable dimension.

  2. Identify where you want the new column to sit. The column is inserted to the right.

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

  4. Click the fx button in the column header to put the fx bar into edit mode, then proceed to enter a formula or function.

  5. (Optional) Change the default name of the new column to something more meaningful.

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

  1. in the white box and type P1 Working Day Value, then press Enter.

  2. Right-click the new column's header and select Edit formula.

  3. When the fx bar is in edit mode, click the first comparison column (Period 1).

  4. Enter the division symbol.

  5. Type W and select WORKING DAYS from the function list that displays.

  6. Press the Open parentheses ( key and select the working day calendar from the list that displays, followed by a comma, then click the first comparison column (Period 1) again and press the Close parentheses ).

  7. Press Enter or Return to complete the formula entry.

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-20240818-221236.pngImage Added
Expand
titleMore help with functions...

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.

  • value1: The first value or column coordinate within the group.         example: '1',

  • [value2...] = Optional, repeatable additional values or column coordinates within the group.         example: '2, 3',

COALESCE

Function: COALESCE(value1, [value2…])

Description:

  • Returns the first non-empty value from the parameters.

  • Value1 is the first value or column coordinate to consider when searching for the first non-empty value. For example, []:[current].

  • Value2... is optional, repeatable additional values or column coordinates to consider when searching for the first non-empty value. For example, 0.

COUNT

Function: COUNT(coordinate1, [coordinate2…])

Description:

  • Returns the number of columns referenced by one or more range coordinates.

  • Coordinate1 is the range coordinate to count the number of referenced columns for. For example, []:[measures;current].

  • Coordinate2... is optional, repeatable additional range coordinates to count the referenced columns for.

FORECAST LINEAR

Function: FORECAST.LINEAR(position, series)

Description:

  • Predicts the value at a given position within the given series using linear regression.

  • Position is the position within the series to calculate the predicted value for. 

  • Series is an array of values or a range coordinate to use as the knowns in the linear regression.   

PERCENTILE

Function: PERCENTILE(series, k)

Description:

  • Returns the kth percentile value for the given series (inclusive of k), where k is a value from 0 to 1.

  • Series is an array of values for which to calculate the kth percentile.     

  • k is the value between 0 and 1 (inclusive) of the percentile rank.       

PERIOD COUNT

Function: PERIODCOUNT(coordinate)

Description:

  • Returns the number of period bands (e.g. months, days) in the period associated with a column.

  • Coordinate is the coordinate of a transaction column to find the period for.    

  • For example, if the []:[current] column references the Rolling 12 Months period, PERIODCOUNT([]:[current]) will return 12 

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. 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
minLevel1
maxLevel2