You must select a dimension to activate this mode; it is not available in Summary view.
Custom mode allows you to design your own version of the grid by defining the columns that it contains, giving you much greater flexibility in the layout and the analysis capabilities.
After you select a dimension, click Mode > Custom or use Alt+C to access the Custom Mode window.
The Custom Mode window displays a representation of the columns that are currently in the grid (except for the property columns). Each row (top to bottom) corresponds to a column (left to right) in the grid, as illustrated in the images below.
You can edit, delete or clone existing columns, add new columns and reorder the columns, as outlined below. After you apply your changes, the grid updates to display your customized column layout and Custom displays in the Mode menu.
Edit a column
Edit the name of the column, if required: Click in the name box and overwrite the name as required. See notes about names in the Add a column section below.
Edit the column configuration settings, if required. The options that are available depend on the type of column (see table and examples below).
Continue customizing the columns or click Apply to view your changes in the grid.
Delete a column
To delete an individual column, click the column’s Clear button.
To delete all columns, click the Clear button at the bottom of the window.
Continue customizing the columns or click Apply to view your changes in the grid.
Move a column
Click and hold the column’s Move button, then drag the column (row) up or down to its new position. This action corresponds to moving the column left or right in the grid.
Continue customizing the columns or click Apply to view your changes in the grid.
Clone a column
Rather than add a new column from scratch, you can clone an existing column and edit its configuration to meet your needs. The new column is represented by a new row at the bottom of the other rows (you might need to scroll down to find it), which has identical settings but the name is appended with the number 1. For example, if you clone the Total Value column, the new column is automatically named Total Value 1. Cloning is a useful option when you want to add a number of similar columns in the grid, as it takes less time to complete.
Click the column’s Clone button.
Editthe new column’s name and settings, as required.
Continue customizing the columns or click Apply to view your changes in the grid.
Add a new column
You can add three types of columns:
A measure is a type of numerical data, such as value, quantity, profit, margin. You can add Measure columns based on any measure in your current database. The Measure columns can include period information, including offset dates and data from different streams.
A variance is the difference between two measures. The Variance column displays either the actual difference value, such as 100,000 or the percentage difference, such as 2%.
If you want to use a Variance column, avoid using the word 'Variance' (with a capital 'V') in the column heading, as this is a reserved word and may affect some functionality.
The Variance and % Variance columns are also available in Variance mode. You can use Variance mode instead, for example, if you want to quickly show the difference between sales this year and sales last year, or the % difference between sales and budget.However, if you use Custom mode, you have mode customization options.
A calculation uses basic mathematical operations to calculate the value that appears in a column, based on existing measures. Basic algebra is used, where variables are defined as a, b, c and so on.
The standard order of operations is followed, which means that multiplication and division operations are processed before addition and subtraction, regardless of position in the expression. You can use brackets to override this order.
It is recommended you use parentheses in calculations. For example, use (a/b)*100, rather than a/b*100.
The available expressions are: + plus, - minus, * multiply and / divide.
For example, if you define variable 'a' as 'profit in January', and 'b' as 'profit in February' and you enter '(a + b)/2' as the expression, the data in each cell of that column will show the average profit for January and February.
There are two special placeholders that can be used in expressions to derive date-based averages:
{PERIOD_COUNT}- If the active period was from Jan 2023 to March 2023, the Period_Count would be 3. An expression such as 'a / {period_count}' would calculate the average value per period for the number of periods active at the time.
{PERIOD_DAYS} - Returns the number of days in each period.
When you add a column, it is represented by a new blank row at the bottom of the other rows (you might need to scroll down to find it).
Click the green New Column button and select one of the following column types: Calculation, Measure or Variance (see descriptions in table below).
Enter a name for the column, keeping the following notes in mind:
Length: The name corresponds to the column heading. The column widths are generated based on the number of characters in the heading, so keep the name as short as possible.
Reserved word: Avoid using the word 'Variance' (with a capital 'V') in the column heading, as this is a reserved word and may affect some functionality.
Offset dates: There are two special placeholders you can use to supply offset dates in the heading; {FDATE} and {TDATE}. These placeholders are replaced at runtime with the From/To date respectively of the period being selected. For example 'Budget {FDATE}' when run for a period starting July 2023 would display 'Budget Jul-2023'.
Configure the column as required. See the table and examples below for more information. Notes on common settings are as follows:
Measure button: Allows you to define the measure on which the column is based.
Format button: Allows you to customize the appearance of the data in each column, such as the number of decimal places and the use of a thousands separator or percentage symbol. Click the Format button (it will say something like ‘1,234.57’) to view your options and select the required format. You can also create a custom format.
Calculated Total options: Allows you to determine how the value in the Total row of that column is calculated. You can select from the following options: Average, Min, Max, Sum Total, Calculated Total, Median and Mode. Some aggregation methods are more suitable than others, depending on the selected measure. See example below.
Append to above option: Allows you to append columns, resulting in two columns sharing a heading. Click the vertical ellipsis button to append a column to the one above (in reality, the column to the left). This makes the columns operate as a single ‘block' and the lower column’s heading becomes disabled (as it now shares the upper heading).
You are unlikely to use this feature on its own, but the functionality is required for Stream mode's multi-stream comparisons, which use double height headers. You can see how it works by going from Stream mode back to Custom mode, where you’ll see appended columns automatically set up.
Cloning, moving or deleting the upper column controls these actions for the ‘block'. Cloning, moving or deleting is disabled for the lower column.
A lock icon displays to the left of the column. Click this icon to detach the column.
Include in global period checkbox: Allows you to override the default behavior, whereby you cannot change the period. If you select this checkbox for a row, the Period menu becomes active for that column. This checkbox usually displays at the end of the column settings row but might be underneath if there is not enough space, as in the following image.
Review your Custom mode settings
To view information about your custom columns and other settings, click the Information button in the top right menu.
To clear all Custom mode settings and return to Period mode, click the Reset button.
Examples of using Custom mode
Example 1: Select the best Calculated Total option
When it comes to selecting the best (most suitable) way to calculate the total, consider the type of measure that is used.
Sum Total v Calculated Total v Average
The most common method to calculate the total is Sum Total. It adds up all the values in the column and returns the total. This is suitable for calculating the total sales value or quantity. However, it is useless when the column contains percentages, such as the margin, as the result is meaningless. Therefore, Calculated Total or Average are better options when dealing with percentages.
In the following image, there are three versions of the July column, which contains the margin values. In each version, a different method is used to calculate the total and as a result, the values in the Total row are different. As you can see, the Sum Total does not make much sense, so Calculated Total or Average would be better options.
Mode
Mode is a statistical function. It returns the most commonly occurring number from a list, or value in a column. It is often used with Median, Mean, Standard Deviations and so on. For example, if the list was 2,5,2,4,6,2,4,4,3,2,2,6,7,9 the mode would be 2.
Example 2: Add Measure columns to compare sales values
Use Custom mode in the Sales database to add four new Measurecolumns in the grid: Sales Last Month, Sales This Month, Budget This Month and Sales Year to Date to analyze the Sales Rep performance.
In the Custom Mode window, delete (clear) all the existing columns.
Click the green New Column button > Measure, then configure column 1 (Sales Last Month):
Type the name 'Sales Last Month'.
Define the measure: Click the Measure button, then select Stream> Sales and Measure > Value. On the Period tab, select the Custom (Month) time unit, selectthe Offset checkboxes and set start and end to -1. Click Apply.
Click the Format button and select the 1,235 option to remove decimal places.
Clone column 1 to create column 2 (Sales This Month), then configure column 2:
Edit the name to be 'Sales This Month'.
Click the Measure button > Period tab, edit the start and end to 0 and click Apply.
Clone column 2 to create column 3 (Budget This Month), then configure column 3:
Edit the name to be 'Budget This Month'.
Click the Measure button > Stream > Budget and click Apply.
Clone column 2 again to create column 4 (Sales Year to Date), then configure column 4:
Edit the name to be 'Sales YTD'.
Click the Measure button > Period tab, clear the Offset checkbox for the start date and select January, then click Apply.
Review your new column configurations and click Apply. The grid updates to display the new columns, as illustrated in the image below.
Save this view as a favorite. Because offset dates were used, it will automatically update.
Example 3: Add a Variance column to calculate the difference between sales and budget values
Use Custom mode in the Sales database to add a variance column in the grid showing the % variance between sales and budget, and two corresponding Measure columns showing the Sales and Budget values to compare the Sales Rep performance.
In the Custom Mode window, delete (clear) all the existing columns.
Click the green New Column button > Variance, then configure column 1 (% variance):
Type the name '% variance'.
Define variable a: Click the Measure button and select Stream> Sales and Measure > Value, then click Apply.
Define variable b: Click the Measure button and select Stream> Budget and Measure > Value, then click Apply.
Click a - b button to switch to a % b calculation. The calculation used is; ((a-b)/b)*100.
Click the Format button and select the 1235% option to display the variance calculation result as a percentage.
Click the green New Column button > Measure, then configure column 1 (Sales):
Type the name 'Sales'.
Click the Measure button and select Stream> Sales and Measure > Value, then click Apply.
Click the Format button and select the 1,235 option to remove decimal places.
Clone column 2 to create column 3 (Budget), then configure column 3:
Edit the name to be 'Budget'.
Click the Measure button > Stream > Budget and click Apply.
Review your new column configurations and click Apply. The grid updates to display the new columns, as illustrated in the image below. You can see sales expressed as a percentage of budget in the new %variance column. Because you didn't define any periods, the default period has been used.
Example 4: Add Calculation columns to get insights on the average profit
Use Custom mode in the Sales database to add three new columns in the grid to display the results of the following calculations: average profit over the last 6 months, profit this month and the difference between the two (to show whether last month is ahead of or behind the average profit).
In the Custom Mode window, delete (clear) all the existing columns.
Click the green New Column button > Calculation, then configure column 1 (Average profit over the last 6 months):
Type the name 'Av profit 6 months'.
Define variable a as the average profit over 6 months:
Click the Add Measure (plus) button > Measure.
Define the measure: Click the Measure button and select Stream> Sales and Measure > Profit. On the Period tab, select the Custom (Month) time unit, selectthe Offset checkboxes and set start to -6 and end to -1. Click Apply.
Define the expression: Enter ‘a/6’ in the Expression box to calculate the average profit over the 6 months.
Click the green New Column button > Measure, then configure column 2 (Profit last month):
Type the name 'Difference'.
Click the Measure button and select Stream> Sales and Measure > Value.
Click the Period tab and select the Custom (Month) time unit, selectthe Offset checkboxes and set start and end to 0.
Click Apply.
Clone column 1 to create column 3 (Difference between the average and this month), then configure column 3:
Edit the name to be 'Sales This Month'.
Define variable a as the total profit over 6 months: Nothing to do here as the cloned configuration is sufficient.
Define variable b as the total profit this month: Click the Add Measure (plus) button > Measure. Define the measure: Click the Measure button and select Stream > Sales and Measure > Profit. On the Period tab, select the Custom (Month) time unit, selectthe Offset checkboxes and set start and end to 0. Click Apply.
Define the expression: Enter ‘b-(a/6)’ in the Expression box to calculate the difference between the average profit over the 6 months and the profit this month.
Review your new column configurations and click Apply. The grid updates to display the new columns, as illustrated in the image below. In this example, the Customer dimension has been selected and the Difference column was sorted in descending order. This will display the customers with above average profit at the top of the list.
Select the best Calculated Total option
When it comes to selecting the best (most suitable) way to calculate the total, consider the type of measure that is used.
Sum Total v Calculated Total v Average
The most common method to calculate the total is Sum Total. It adds up all the values in the column and returns the total. This is suitable for calculating the total sales value or quantity. However, it is useless when the column contains percentages, such as the margin, as the result is meaningless. Therefore, Calculated Total or Average are better options when dealing with percentages.
In the following image, there are three versions of the July column, which contains the margin values. In each version, a different method is used to calculate the total and as a result, the values in the Total row are different. As you can see, the Sum Total does not make much sense, so Calculated Total or Average would be better options.
Mode
Mode is a statistical function. It returns the most commonly occurring number from a list, or value in a column. It is often used with Median, Mean, Standard Deviations and so on. For example, if the list was 2,5,2,4,6,2,4,4,3,2,2,6,7,9 the mode would be 2.
Add Measure columns to compare sales values
Use Custom mode in the Sales database to add four new Measurecolumns in the grid: Sales Last Month, Sales This Month, Budget This Month and Sales Year to Date to analyze the Sales Rep performance.
In the Custom Mode window, delete (clear) all the existing columns.
Click the green New Column button > Measure, then configure column 1 (Sales Last Month):
Type the name 'Sales Last Month'.
Define the measure: Click the Measure button, then select Stream > Sales and Measure > Value.
On the Period tab, select the Custom (Month) time unit, selectthe Offset checkboxes and set start and end to -1 and click Apply.
Click the Format button and select the 1,235 option to remove decimal places.
Clone column 1 to create column 2 (Sales This Month), then configure column 2:
Edit the name to be 'Sales This Month'.
Click the Measure button > Period tab, edit the start and end to 0 and click Apply.
Clone column 2 to create column 3 (Budget This Month), then configure column 3:
Edit the name to be 'Budget This Month'.
Click the Measure button > Stream > Budget and click Apply.
Clone column 2 again to create column 4 (Sales Year to Date), then configure column 4:
Edit the name to be 'Sales YTD'.
Click the Measure button > Period tab, clear the Offset checkbox for the start date and select January, then click Apply.
Review your new column configurations and click Apply. The grid updates to display the new columns, as illustrated in the image below.
Save this view as a favorite. Because offset dates were used, it will automatically update.
Add a Variance column to calculate the difference between sales and budget values
Use Custom mode in the Sales database to add a variance column in the grid showing the % variance between sales and budget, and two corresponding Measure columns showing the Sales and Budget values to compare the Sales Rep performance.
In the Custom Mode window, delete (clear) all the existing columns.
Click the green New Column button > Variance, then configure column 1 (% variance). Type the name '% variance':
Define variable a: Click the Measure button and select Stream> Sales and Measure > Value, then click Apply.
Define variable b: Click the Measure button and select Stream> Budget and Measure > Value, then click Apply.
Click a - b button to switch to a % b calculation. The calculation used is; ((a-b)/b)*100.
Click the Format button and select the 1235% option to display the variance calculation result as a percentage.
Click the green New Column button > Measure, then configure column 1 (Sales):
Type the name 'Sales'.
Click the Measure button and select Stream> Sales and Measure > Value, then click Apply.
Click the Format button and select the 1,235 option to remove decimal places.
Clone column 2 to create column 3 (Budget), then configure column 3:
Edit the name to be 'Budget'.
Click the Measure button > Stream > Budget and click Apply.
Review your new column configurations and click Apply. The grid updates to display the new columns, as illustrated in the image below. You can see sales expressed as a percentage of budget in the new %variance column. Because you didn't define any periods, the default period has been used.
Add Calculation columns to get insights on the average profit
Use Custom mode in the Sales database to add three new columns in the grid to display the results of the following calculations: average profit over the last 6 months, profit this month and the difference between the two (to show whether last month is ahead of or behind the average profit).
In the Custom Mode window, delete (clear) all the existing columns.
Click the green New Column button > Calculation, then configure column 1 (Average profit over the last 6 months):
Type the name 'Av profit 6 months'.
Define variable a as the average profit over 6 months. Click the Add Measure (plus) button > Measure. Define the measure: Click the Measure button and select Stream> Sales and Measure > Profit. On the Period tab, select the Custom (Month) time unit, selectthe Offset checkboxes and set start to -6 and end to -1. Click Apply.
Define the expression: Enter ‘a/6’ in the Expression box to calculate the average profit over the 6 months.
Click the green New Column button > Measure, then configure column 2 (Profit last month):
Type the name 'Difference'.
Click the Measure button and select Stream> Sales and Measure > Value.
Click the Period tab and select the Custom (Month) time unit, selectthe Offset checkboxes and set start and end to 0.
Click Apply.
Clone column 1 to create column 3 (Difference between the average and this month), then configure column 3:
Edit the name to be 'Sales This Month'.
Define variable a as the total profit over 6 months: Nothing to do here as the cloned configuration is sufficient.
Define variable b as the total profit this month. Click the Add Measure (plus) button > Measure. Define the measure: Click the Measure button and select Stream> Sales and Measure > Profit. On the Period tab, select the Custom (Month) time unit, selectthe Offset checkboxes and set start and end to 0. Click Apply.
Define the expression: Enter ‘b-(a/6)’ in the Expression box to calculate the difference between the average profit over the 6 months and the profit this month.
Review your new column configurations and click Apply. The grid updates to display the new columns, as illustrated in the image below. In this example, the Customer dimension has been selected and the Difference column was sorted in descending order. This will display the customers with above average profit at the top of the list.
